formula - change date format excel

Question

I have the following date in excel "5/2/2020", I would like to obtain "5/Feb/2020", but unfortunately I am getting "May/2/2020".

Also tried to change the format of the cells , but excel still interprets that the first number is the month.

How do I tell Excel that the month is in the second position ?

question from:https://stackoverflow.com/questions/66064830/change-date-format-excel

Answer 1

Try: If 5/2/2020 is in cell E5 then:

=VALUE(TEXT(E5,"d/mm/yyyy"))

and then custom number format this cell to d/mmm/yyyy

will yield 5/Feb/2020