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?

 

[erind]

Starting with a date of 11/12/2018 this changes to 43416, and then reformatted is 12/11/2018.

Starting with a date of 09/13/2018 this gets a results of #NUM !

The only value I replaced here is the A2 to refer to my cell. Is that how it should be? I wasn't sure what the 4, +1 or -1 are referring to.

 

[MARK858]

You can't get 12/11/2018 with a format of dd mmm yyyy.

 

[erind]

Ahh, got it. So that changes the first half of my numbers to the correct date, 12-Nov-18, which I can now reformat to 11/12/2018. Perfect.

Any idea how to fix the other ones? They come up with an error: #NUM !

 

[MARK858]

Afraid not as a text string of 09/13/2018 converts to 13 Sept 2018 for me. Can you just put inthe formulas

=ISNUMBER(A2)

and

=LEN(A2)

changing the A2 to the cell that contains 09/13/2018 and let me know what they return.

 

[erind]

=ISNUMBER(A2) results in FALSE

=LEN(A2) results in 7

For the previous date that we already figured out it returns TRUE and 16.

 

[MARK858]

7? are you sure sure that your cell isn't showing something like 9/13/18? If it was then you would get a NUM error.

 

[MARK858]

Try the formula below....

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

 

[erind]

This worked perfectly! Thank you so much!