How to Convert Date to Number in Excel

- Written by Puneet

Excel stores a date as a number. Each date has a serial number value that is unique. If you see in the following example, you have a date in cell A1 “1-Jan-2022”.

date-in-formula-bar

But if you look at the formula bar it shows the date, not the serial number. So, in this tutorial, you will learn to convert a date into a serial number.

Get the Excel File

Convert the Date into a Number

As I said in the starting Excel stores dates as serial numbers, and you can convert a date into a serial number simply by changing its formatting.

convert-date-to-number
  1. First, select the cell where you want to convert the date into a number.
  2. Next, open the custom formatting by using the keyboard shortcut Ctrl + 1.
  3. Now, from the category options select the number format.
  4. Last, specify the decimals to zero and click OK.

Once you change the formatting you will get the number just like the following where you have the number instead of the date.

date-as-number

Convert Text Date into a Number

There could be a situation where you have a date in a cell that is stored as a text and now you want to convert it into a number. In this case, you need to use the DATEVALUE which converts a date into a date serial.

text-date-into-number
  1. Enter the “=DATEVALUE” in a cell.
  2. Then enter starting parentheses.
  3. Refer to the cell where you have the date.
  4. Now, enter closing parentheses and hit enter.

Get the Excel File

Leave a Comment