Convert Date to Number

HomeExcel FormulasConvert Date to Number

Excel stores 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.

Convert Date into a Number

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

convert-date-to-number

Use the following steps:

  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 function which converts a date into a date serial. See the following example:

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.

🔙 Excel Formulas \ 📂 sample-file