How to Convert Unusual Date Text Imported to Excel

When we import data into Excel, dates can arrive in formats that Excel wasn't designed to translate. Here are some ways to make sense of unusual dates.

318

When we import data into Excel, dates can arrive in formats that Excel wasn't designed to translate. Here are some ways to make sense of unusual dates.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.

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.

Example of 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.

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 the command Data, Data Tools, 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.