How to Convert Unusual Date Text in Excel

by Charley Kyd on January 18, 2011

When you import data into Excel, dates often arrive in formats that Excel doesn’t recognize. This is particularly true if your data comes from another country.

Excel's DATEVALUE function

Excel's DATEVALUE function used in column B fails with these formats and many more. Some other date-conversion technique is needed.

This figure shows an extreme example. Column A contains a variety of date formats, and column B shows how this formula…

B1:    =DATEVALUE(A1)

…fails to convert them.

The common way to convert these dates is to use the DATE function along with text functions like LEFT, RIGHT, MID, and FIND.

But there’s an easier way.

As you probably know, if you open a text file in Excel, it typically arrives in column A. You then can use this command in…

  • In New Excel…Data, Data Tools, Text to Columns
  • In Classic Excel…Data, Text to Columns

…to split the column into its component parts.

However, you also can use the Text-to-Columns command to convert a single column of unusual date formats into date serial numbers.

Just select the dates in column A; launch the Text-to-Columns command; choose Next; and choose Next again. In Step 3 of 3, choose the Date option; choose the correct M, D, and Y sequence in the adjacent list box; and then choose Finish. When you do so, Excel converts the date text to date serial numbers.

Previous post:

Next post: