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