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

tobrien2

New Member
Joined
Sep 21, 2017
Messages
2
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
C:\Users\Lenovo\AppData\Local\Temp\msohtmlclip1\01\clip_image002.jpg
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,031
Office Version
  1. 2010
Platform
  1. Windows
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 .

Code:
=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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,130,447
Messages
5,642,209
Members
417,262
Latest member
andrewd1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top