Formatting Dates

[erind]

I received an excel doc with a date column where the dates were all in different formats, some just dates and some with time stamps. I have removed the time stamps, and now I am trying to format the cells so that they are all the same. Currently they are showing as mm/dd/yyyy, but excel is reading it as dd/mm/yyyy, so when I try to format cells the only change i can make flips the month and day for half the numbers, and it is not recognizing the other values as proper dates (anything after the 13th of the month, as it thinks this is the month value).

Is there a way to tell excel that the dates are already showing as mm/dd/yyyy, and not the other way around?

 

[mole999]

what happens if you format using mmm and get the month name

 

[erind]

For some, it still switches the month/day, so 11/12/2018 becomes Dec 11, but should be Nov 12.

For the others it doesn't change at all, as it is not recognizing the middle number as a month if it is 13 or over.

 

[GR00007]

Sounds like you have quite a mixture of text and date values in that column.
You might try a helper column with =IFERROR(DATEVALUE(A2),A2) and format the result as date and look at the result then.

 

[erind]

When I try this formula I get a result of 43110, no matter what the date starts as. When I try to format that to a date it gives me January 10, 2018.

Is there a way to change it all to text? When I do a "paste value" it gives me a 5 digit number, rather than just posting what is shown. If I could just get it all to a point where I can sort it all it would be fine, but right now anything with a number above 12 in the middle space goes to the end.

 

[mole999]

was the date supposed to be 10/1/18. what is the data source ?

 

[erind]

Sorry, yes, the date displayed as 10/01/2018, which should be read as Oct 1, 2018, turns into 43110, and then January 10, 2018 when I reformat it.

 

[erind]

And the dates with a number >12 in the middle do not change at all with that formula.

 

[MARK858]

What happens if you put in the formula below and format it as dd mmm yyyy?

=IFERROR(IF(ISNUMBER(A2),VALUE(TEXT(A2,"mm/dd/yyyy")),DATE(RIGHT(A2,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,2))),DATE(RIGHT(A2,4),LEFT(A2,FIND("/",A2)-1),MID(A2,FIND("/",A2)+1,1)))

 

[GR00007]

When I try this formula I get a result of 43110, no matter what the date starts as. When I try to format that to a date it gives me January 10, 2018.

Is there a way to change it all to text? When I do a "paste value" it gives me a 5 digit number, rather than just posting what is shown. If I could just get it all to a point where I can sort it all it would be fine, but right now anything with a number above 12 in the middle space goes to the end.

Are all the rows 5 digit numbers? If not then those rows are still text. The 5 digit numbers can be formatted many ways:
- m/d/yyyy would be 10/1/2018
- d/m/yyyy would be 1/10/2018
- mmm dd yyyy would be Oct 01 2018

For a cell that is text it will remain as text.