Imported date field not recognized in Excel

A while back a client was having some problems with an imported text file in Excel. There were date columns that he needed to be formatted as dates so that he could calculate with them (i.e. # of days elapsed between two dates). In Excel, he tried a Text to Columns and change the date columns to dates, but the date displays as a series of ###.

The reason he was having problems is that the date is in unrecognizable format for data conversion. The date is in YearMonthDay format (in image above date displays as 20050219). For conversion purposes, Excel is looking for the more traditional MonthDayYear. That’s why he got the ####’s because the serial value is huge, probably to the end of time (or maybe back to the beginning).

In four basic steps, I rearrange the data, convert it to a serial value, and format it as a recognizable date, which can be calculated. In the image below, I’ve broken the process into 4 distinct steps so you can see what is happening.

1. Use combination of concatenate and text functions to rearrange your date text values. =CONCATENATE(MID(M2,5,2),”/”,RIGHT(M2,2),”/”,LEFT(M2,4))
2. Copy the formulas and use a Paste Special, Values only to get a raw text value in the proper MonthDayYear sequence.
3. Use the DateValue function to convert the text date value to a serial value. The only way Excel can calculate a date.
   =DATEVALUE(P2)
4. Format the serial value into desired date format.

Did you enjoy this post? Why not leave a comment below and continue the conversation, or subscribe to my feed and get articles like this delivered automatically each day to your feed reader.