How To Change A Date Into A Serial Number Recognised By Excel

2017-04-23

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!

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Subscribe

Advertisement

Related Articles

37 Awesome Excel Mouse Tips & Tricks You Should Know

37 Awesome Excel Mouse Tips & Tricks You Should Know

While the keyboard is generally quicker, you shouldn’t completely ignore the mouse. There are also some great time saving mouse shortcuts as well. In this post we’ll take a look at some of the best Excel mouse time saving tips and tricks.

read more
25 Amazing Power Query Tips and Tricks

25 Amazing Power Query Tips and Tricks

Power query is amazing tool that allows you to import and transform data with ease and helps to create repeatable and robust procedures with your data. Here are some tips and tricks to help you get the most out Power Query.

read more

Comments

0 Comments

Get The Latest News

Follow Us

Follow us on social media to stay up to date with the latest tips in Excel!

Pin It on Pinterest

Share This