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”.
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.
Use the following steps:
- First, select the cell where you want to convert the date into a number.
- Next, open the custom formatting by using the keyboard shortcut Ctrl + 1.
- Now, from the category options select the number format.
- 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.
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:
- Enter the “=DATEVALUE” in a cell.
- Then enter starting parentheses.
- Refer to the cell where you have the date.
- Now, enter closing parentheses and hit enter.
More on Date-Time Formulas
Add Minutes to Time | Add Month to a Date | Add Years to Date | Add-Subtract Week from a Date | Calculate Age using Date of Birth | Change Time Format | Compare Two Dates | Convert Date to Number | Create a Date Range | DATEDIF Function | Get Day Number of Year | Get Month from a Date | Get Quarter from a Date | Get Years of Service | Calculate Time Difference Between Two Times | Get Day Name from a Date | Get End of the Month Date | Get Total Days in Month | Highlight Dates Between Two Dates | Military Time (Get and Subtract)