Get The Completed Workbook

Excel allows a variety of date formats and depending where you’re from the default formats will be different. A date like 31st December 2016 might appear formatted in Excel in any of these ways:

• Dec, 31 2016
• 2016-12-31
• 31-12-2016
• 12/31/2016 (mm dd yyyy format is only used in the US)
• 31/12/2016
• 16-Dec-31

There are many more possible formats and you can even create your own with custom formats. In this example of Dec, 31 2016, the date is really stored in Excel as the value 42735 regardless of the format. This is because Excel stores dates as a serial number between 1 and 2,958,465 which represent dates between 01/01/1900 and 31/12/9999. The goal of the date serial number is to avoid having Excel view something like 31/12/2016 as different from 16-Dec-31 because they are written differently. For the most part this system works pretty well.

You may have come across data that is supposed to be a date but doesn’t actually use the correct serial number. For example, maybe someone has tried to use a yyyymmdd format and has entered 20161231 into a cell. The problem with this is that Excel will actually recognise this as a regular number with general formatting. It will look like a date but we won’t be able to use any of Excel’s date functions properly. We would need to convert these to the proper serial number value before using any built in date functions.

Converting Dates with Formula

We could convert dates like these by using some text formulas to parse out the year part, month part and day part of the number.

=DATE(LEFT(Date,4),RIGHT(LEFT(Date,6),2),RIGHT(Date,2))

Where Date is the date number we are tying to convert into a proper serial number date. This formula takes the left 4 characters as the year, the middle two characters as the month and the last two characters as the day and then converts this information into the Excel date serial number.

Since this date is a regular number, we could also do the parsing using numerical functions.

=DATE(INT(Date/10000),INT((Date-INT(Date/10000)*10000)/100),(Date/100-INT(Date/100))*100)

Convert Dates with Text to Column

If you don’t feel like using formulas there is also a way to do this in Text to Column.

Open the Text to Columns editor.

1. Highlight the data containing the dates in the general format that you want to convert to a serial number.
2. Go to the Data tab in the ribbon.
3. Select Text to Columns from the Data Tools section.

Select Delimited from the options and press the Next button.

Deselect all options from the Delimiters section and press the Next button.

Step 3 of the Convert Text to Columns Wizard.

1. Select Date from the Column data format options and select YMD from the drop down.
2. Choose the Destination where the converted data will appear. By default it will overwrite your data, but you can select a new location so your original data is not overwritten.
3. Press the Finish button.

Your dates will now be in the proper Excel serial number format!