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

date-to-number

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

Date as a number? What are date codes 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 them 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 for speeding 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. The next chapter will examine what happens 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 four 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
convert-number-to-date

Conclusion

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

Related Formulas: