How to Convert Date to Number in Excel (4 Methods)

In many situations, we need to convert Date to Number in Excel to accomplish a calculation. Working with Date and Time values is one of the trickiest parts of Excel. People store Dates in different formats as the combination of day, month, and year. But what Excel does to recognize a Date? It stores Dates as a number in the backend. In this article, we’ll get to know the methods that transform a Date into a Number.


Download the Practice WorkBook

Download this practice workbook to exercise while you are reading this article.


4 Methods to Convert Date to Number in Excel

In this article, we’ll describe 4 easy step-by-step methods to convert Date to Number in Excel. Here is an overview of the final result.

NOTE: As we know Excel stores Dates as a serial number in its system. This serial number starts from 1 on the date 1/1/1900 and increases by 1 onwards.

Convert Date to Number in Excel


1. Transform Date to Serial Number in Excel Using the DATEVALUE Function 

The DATEVALUE function is Excel converts a text formatted date to a serial number that is recognizable as a date to Excel.

Syntax of the DATEVALUE function:
=DATEVALUE (date_text)
where date_text is the only argument.

Let’s follow the example:

1.1 Argument of the DATEVALUE Function in the Date Format

If the argument for the DATEVALUE function is in the Date Format, then we need to put the date inside double quotes to make the function work. See the screenshots below:

Screenshot 1: We can see that the selected column is in the Date Format.

Convert Date to Number in Excel

Screenshot 2: We put the date inside a double quote to make it text for the DATEVALUE function and then hit Enter.

Convert Date to Number in Excel

The DATEVALUE function converted the date into a serial number.


1.2 Argument of the DATEVALUE Function in the Text Format

If the argument for the DATEVALUE function is in the Text Format, then we just need to put the date inside the function to transform it into a serial number. Let’s follow the screenshots:

Screenshot 1: Here the selected cells contained a list of dates are in Text Format. 

Convert Date to Number in Excel

Screenshot 2: Here in cell H6 we put F6 as the argument which contains the date 1/1/2022 (in Date Format) and hit Enter to convert it to a Serial Number.


2. Convert Date to Serial Number Using Home Tab of Excel Ribbon

In this method, we’ll use the Home Tab of the Excel Ribbon to transform a date into a 5-digit serial number. Let’s follow these easy steps:

  • This screenshot shows a list of dates in the Date Format. From the Home Tab, navigate to the Number section, there is a box that shows the format of the selected cells and options to change into another format.

Convert Date to Number in Excel

  • Now from the format options choose the General or the Number option.

Convert Date to Number in Excel

  • The above step will turn the date into a 5-digit serial number.

Similarly, we can have all other dates converted into a serial number.

 Convert Date to Number in Excel


Similar Readings


3. Use of Cell Formatting for Converting Date to Number

Open the Cell Formatting Option (3 ways):

  • The context menu in Excel provides the option of Cell Formatting name Format Cells. With the options of the Cell Formatting, we can change the format for the selected cell. We can open the context menu by simply clicking the right button of our mouse on the selected cell.

 Convert Date to Number in Excel

  • We can also go to the Cells section from the Home Tab. Then from the Format Tab select the Format Cells option.

  • Press Alt + H + O + E on your keyboard to make the Format Cells window visible.

Now that you have the Format Cells window opened, in the Number tab select General from the Category list. This time we selected all the cells that contain dates, together. Finally, don’t forget to click the OK button.

Convert Date to Number in Excel

Look at the result.


4. Using Cell Formatting to Transform Date to 8-Digit Number (mmddyyyy or ddmmyyyy Format) 

By following the ways (click here to see the ways) described in the previous method, we can open the cell formatting options easily. Then follow the steps below:

  • Go to the Number Tab.
  • Select Custom option from the Category
  • Put mmddyyyy in the Type input box.
  • Finally, click the OK button

Convert Date to Number in Excel

  • The above steps converted all the dates into 8-digit numbers in the mmddyyyy format. We can see the first 2 digits represent the month, the following 2 digits represent the day, and the last 4 digits are the year.

Convert Date to Number in Excel

Following the same procedure, we can have different formats like ddmmyyyy, yyyymmdd, etc.

Here is an overview:


Things to Remember

  • Sometimes it may happen while converting a Date to a Number, the result shows #### in the cell. It happens when the cell width is not enough to hold the serial numbers. Increasing the cell width will solve it immediately.
  • While using the default date system of Microsoft Excel for Windows the argument value must be in a range from January 1, 1900, to December 31, 9999. It fails to handle a date beyond this range.

Conclusion

Now, we know the methods to convert Date to Number in Excel, it would encourage you to use this feature more confidently. Any questions or suggestions don’t forget to put them in the comment box below


Further Readings

Al Arafat Siddique

Al Arafat Siddique

Hello! This is Arafat. Here I'm researching Microsoft Excel. I did my graduation from Bangladesh University of Engineering and Technology(BUET). My interest in data science and machine learning allured me to play with data and find solutions to real-life problems. I want to explore this data-driven world and make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo