Excel formatted dates incorrectly, how to convert to correct dates?

[tobrien2]

I imported a text file into excel, and excel apparently thought the days were months and the months were days.

How can I convert these dates to be in the appropriate format?

For example, in the first row, the actual date is July 1st, 2017 18:55:57, but excel reads it as January 7th, 2017 18:55:57

 

[tobrien2]

Here's a screenshot

 

[admiral100]

Hi,

Maybe this:-

=TEXT(A1,"mm/dd/yy hh:mm:ss")+0

 

[joeu2004]

All or some of the data in column D should be text because Excel cannot (mis)interpret 13/7/2017 as month 13 day 7, for example.

But I suspect even 1/7/2017 is text, despite appearances, because Excel could not (mis)interpret that as a date (m/d/yyyy) due to extraneous characters, possibly non-printing spaces (HTML nbsp; ASCII 160). You could use =ISTEXT(D2) to confirm.

To cover all possibilities and rely on the fewest assumptions, try the following formatted as Custom m/d/yyyy hh:mm:ss .

=IF(ISNUMBER(D2), --TEXT(D2, "d/m/yyyy h:m:s"),
DATE(MID(D2, FIND(".",SUBSTITUTE(D2,"/",".",2))+1, 4),
MID(D2, FIND("/",D2)+1, FIND(".",SUBSTITUTE(D2,"/",".",2)) - FIND("/",D2) - 1),
LEFT(D2, FIND("/",D2)-1)) + MID(D2, FIND(":",D2)-2, 8))

The first part applies when Excel did indeed (mis)interpret the data as a date in the (incorrect) form m/d/yyyy. The double-negate converts the text result to numeric date and time.

The second part applies when Excel could not (mis)interpret the data, and it simply treated it as text instead. We do not need to double-negate to convert text to numeric date and time because the addition ("+") has that effect.