How to convert dates to numbers in Excel

In Excel, dates can be stored as both serial numbers and actual date formats. The reason for converting dates to serial numbers in Excel is to perform mathematical calculations and operations with the dates. When you perform calculations with dates in Excel, it uses the serial number format to perform the calculations. Furthermore, there are certain functions in Excel for example NETWORKIDAYS which require the dates to be converted to serial numbers first and then to be used in the function. However, this function does this conversion from date to serial number by itself, as soon as you input any date into this function.

In the dataset below, we have information about different products bought and sold on different dates, along with their buying prices, selling prices, and the resulting profit. We can use this data to perform various calculations and analyses and for doing that we need to convert it into serial numbers. 

The basic methods to do this which are as follows:

• Using the context menu to change the format of dates
• Using the Number group options on Home Tab

Method 1 – Using Format cells to convert into numbers

Step 1 – Selecting the cells

• If you want to convert dates in cells to serial numbers, you’ll have to select the cells in which the dates are present. 
• For selecting the cells, move your mouse cursor over one cell in which the date is present.
• After that, select the cell by left-clicking it.
• Then, select multiple cells by pressing the Shift key and using the arrow keys for selecting cells.

Step 2 – Open format cells dialogue box

• After selecting the cells, press right-click button and this will open the context menu.
• Now, locate and click on the format cells option.
• Once you have completed the previous step, a pop-up window called “Format Cells” will be displayed on your screen. Alternatively, you may use the CTRL+1 shortcut key to open the same dialog box.

Step 3 – Converting dates into numbers

• After performing the above step, you have to locate the second option named Number.
• Then left-click on it and new options will appear in the dialogue box.
• Then click on the OK option and all the dates in the cells which you have selected would be converted into numbers.
• Here the value 44562 in cell A2 tells us that it is 44562 days from 1^st January, 1900.

Method 2: Using the Number Group option on the Home tab

Step 1 – Select all cells

• If you want to convert dates in cells to serial numbers, you’ll have to select the cells in which the dates are present. 
• For selecting the cells, move your mouse cursor over one cell in which the date is present.
• After that, select the cell by left-clicking it.
• Then, select multiple cells by pressing the Shift key and using the arrow keys for selecting cells.

Step 2 – Locate the Number group on the Home Tab

• Now locate the Number group on the Home tab.
• Click on the drop-down menu arrow to open the menu and get the options to change the number format as shown below.

Step 3 – Change the date format to numbers

• From the drop-down menu, choose the option Number. This will convert the dates to simple numbers as desired.