Convert Date to Number [or Number to Date] in Excel

This tutorial will show you how to convert a date to a number or convert a serial number to a date using Excel.

Table of contents:

  1. Date as a number? What are date codes in Excel?
  2. How to convert Excel date to number
  3. Convert date stored as text to number
  4. Convert number to date

Date as a number? What are date codes in Excel?

In Excel, the idea of “date as a number” relates to how Excel represents and stores dates. Instead of storing dates as text values, Excel denotes dates as serial numbers. Each date has a unique serial number. For example, the first date is January 1, 1900, equivalent to 1, where 1 is an integer value. Excel increments the numbers by 1 from day to day.

So, if the selected date is January 1, 2020, the serial number is 43831. Using date codes or serial numbers allows users to quickly calculate differences between dates, add or subtract days, and use dates in various functions and formulas.

Today’s guide will show you the best practices and formulas. With its help, you can speed up your custom tasks.

How to Convert Excel Date to Number

Here are the steps to convert an Excel Date to a serial number:

  1. Select the cell that contains a date.
  2. Go to the “Home” tab on the Excel ribbon.
  3. In the “Number” group, choose “Number” from the drop-down list of formats.

Here is a live preview of how it works.

convert date to a serial number

The conversion process can take only seconds. In the next chapter, we will look closely at what will happen if the cell contains a date but the value is stored as text.

Convert Date Stored as Text to Number

Sometimes, you can face an issue: Excel does not recognize the date correctly and identifies the date as a text. To convert a date stored as text to a number in cell B2, use the DATEVALUE function.

Enter the formula in cell D2:

=DATEVALUE(B2)
Date Stored as Text to Number

Create valid dates using the DATE function

The DATE function uses three arguments: year, month, and day. The challenge is to extract the characters from a string using custom text functions. This function can be helpful when you import data from a third-party application (for example, custom ERP systems) and Excel does not recognize the date format.

The LEFT function extracts the first 4 characters from the left, and we get the year from the text string:

=LEFT(20221231, 4) = 2022

The MID function extracts the month from the text:

=MID(B3,5,2) = 12

Finally, the RIGHT function strips two characters from the right:

=RIGHT(B3,2) = 31
Create valid dates using the DATE function

Now, we have all three arguments for the DATE function. Here is the formula to create date-stored text to valid dates:

=DATE(LEFT(B3,4),MID(B3,5,2),RIGHT(B3,2))

Convert Number to Date

You can change the cell formatting to convert a serial number to a date.

To create a date from a number, use the following steps:

  1. Select the cell that contains a number
  2. Click on the Home Tab and locate the Number Group
  3. Select the “Date” format using the drop-down list
format cell

Conclusion

Thanks for being with us today. Understanding the concept of “date as a number” in Excel is important for any data analysts working with dates and times.

Additional resources:

Istvan Vozar

Istvan is the co-founder of Visual Analytics. He helps people reach the top in Excel.