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.

What is a Date in Excel? In Excel’s date system, dates are equal to serial numbers. For example, the first date is January 1, 1900, equivalent to 1, where 1 is an integer value. Excel increment the numbers by 1 from day to day. So, if the selected date is January 1, 2020, the serial number is 43831.

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

Date to Number conversion

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

  1. Select the cell that contains a date
  2. Click on Home Tab, and locate the Number Group
  3. Select the “Number” format using the drop-down list
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.

Date Stored as Text to Number

Sometimes you can face an issue: Excel does not recognize the date correctly and identify 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 strip 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))

Number to Date

You can change the cell formatting if you need 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 Home Tab, and locate the Number Group
  3. Select the “Date” format using the drop-down list
format cell

Additional resources:

Istvan Vozar

Istvan is the co-founder of Visual Analytics Ltd. He writes blog posts and helps people to reach the top in Excel.