Converting Long Text Dates to Serial Numbers in Excel

Here’s a question that came our way recently from a participant in one of our classes. He had a spreadsheet with a long column of dates that had been entered or downloaded as strings of text. So the entries looked like this: April 1, 2012. Remember that dates in Excel can only be used properly (i.e. in formulas, graphs, filters, pivot tables, etc.) when Excel recognizes them as a serial number. Each day in Excel is ascribed its own unique serial number, with January 1, 1900 being day 1 and so on. But text strings that look like dates aren’t recognized. So how would we turn the text “April 1, 2012” into a serial number?

The first function that’s important here is the DATE function. The syntax of the DATE function is =DATE(year,month,day). As long as we can find a way to extract the NUMERIC value of the year, month and day from the text string, we can then embed these values into the DATE function and convert the information to a serial number. In the example below, we used a combination of text functions to pull out the year, month and day in separate cells. We then used the DATE function to combine these values. This work could have been done by embedding the text functions inside the DATE function, but the formula would have been way too long. Note that for the month value, we created a little table with the values 1-12 for the text month names, and used a VLOOKUP to ascribe a value to the extracted the name of the month.

The result in cell B7 is a true serial number.

Believe it or not, there is a much faster way to do this. In a blank cell, multiply the text date by 1 (the formula would be =A3*1) and this will convert it as well! This will work with a variety of text date formats, but not all.

Nonetheless, knowing these text functions how they can be used is critical if you want to be an efficient Excel user.