How to Convert Date to Serial Number in Excel?

by

Most Excel users work with dates on a daily basis.

Working with dates can be a bit challenging as the same date can be formatted differently. However, in the back end, all dates are stored as serial numbers.

For example, the date January 1, 1900, is stored as serial number 1 in the backend. Similarly, January 2, 1900, is stored as 2, and so on.

In this tutorial, I will show you some simple methods you can use to convert a date into the corresponding serial number.

If you have a valid date that Excel recognizes and stores as a serial number in the backend, it’s quite easy to get the serial number (as you only need to change the format of the cells). And in case the date is in a text format, then you will need to use some formulas to get this done.

Method 1: Convert Date to Serial Number Using the Format Option in the Ribbon

You can convert the date into a serial number using the Number Format option available in the Home tab.

Below, I have a sample data set where I have employee names in column A and their Date of birth (DOB) in column B. 

dataset with dates

In the above data set, DOB is in date format, and I want to convert these dates into serial numbers. 

Below are the steps to do this by changing the format of the cells that contain the dates:

  1. Select the date that you want to convert into the serial number. For this example, I am going to select the DOB column. To select a range, select a cell and then drag over the other cells with the left mouse button pressed.
Select the cells with dates
  1. Click on the Home tab in the ribbon
Click the home tab
  1. In the Number section, click on the Number Format dropdown
click the date option in the ribbon
  1. From the drop-down select the Number option
select the number option

This will convert all the selected dates to serial numbers as shown in the below screenshot.

dates converted to serial numbers

The above method will work only when the date is in Excel recognizable format.

Excel recognizes various date formats. You can check the Excel-supported formats from the Format Cell option. In order to do so select a cell containing a date and press CTRL + 1 from the keyboard to open the format cell option. In the Format Cells dialog box, click on the Date option in the side menu and you can see various date formats supported by Microsoft Excel.

Also read

- How to Convert Date to Day of Week in Excel?
- Convert Number of Days to Weeks in Excel
- How to Convert Date to Quarter in Excel

Method 2: Convert Date into Serial Number Using the Format Cells Dialog Box

In this section, I am going to show you how you can convert the date into a serial number using the Format Cell option.

For demonstration purposes, I have a sample data set where I have employee names in column A and their Date of birth (DOB)  in column B. 

dataset with dates

In the above data set, DOB is in date format, and I want to convert these dates into serial numbers. 

Below are the steps to do this by changing the format of the cells using the Format Cells dialog box:

  1. Select the date that you want to convert into the serial number. For this example, I am going to select the DOB column.
select cells with dates
  1. Click on the Home tab in the ribbon
click the home tab
  1. Click on the Format option 
click the Format option
  1. From the drop-down that shows up click on the option Format Cells
Click on Format cells
  1. This will open the Format Cell dialog box as shown below
Format Cell dialog box
  1. Select the Number option in the Number tab
Select the Number option
  1. [Optional] If you want the serial number in decimals, specify the decimal places you want. In this example, I don’t want any decimal digit therefore I am setting its value equal to zero.
Specify the decimal places
  1. [Optional] Similarly if you want to separate the serial number by (,) after 1000 check the Use 1000 separator (,) checkbox. But for this example, I am going to uncheck it in order to keep things simple.
uncheck the thousand separator
  1. Click on OK 

This will convert the selected date into a serial number as shown in the below screenshot

dates converted to serial numbers

This is a bit longer method for converting the date into a serial number, however, this method will give you more control as compared to Method 1.

For instance, using this method, you can specify the decimal places and thousand separators.

Pro Tip: You can use the keyboard shortcut Control + 1 to open the Format Cells dialog box. To use this, select the cells that you want to format, hold the Control key, and then press the 1 key

Also read: How to Convert Serial Numbers to Date in Excel

Method 3: Convert Date in Text Format to Serial Number Using DATEVALUE Function

In the first two methods, we see how we can convert the date into a serial number but that method would not be applicable when the date is in text format. 

However, most of the time we have dates in text format.

For example, when we copy and paste data from an external source like the web to Excel, dates are copied in text format. 

So, how to convert the date (in text format) to a serial number? You can do this by using the DATEVALUE formula. 

DATEVALUE is a built-in Excel formula that could be used in order to convert dates (that are in text format ) to the corresponding serial number.

Let’s see how it works by doing an example.

For demonstration purposes, I have a sample data set where I have employee names in column A and their Date of birth (DOB) in column B. 

dataset with dates

As shown in the screenshot below, the dates in Column B are in text format (see the highlighted format in the ribbon)

dates are in the text format

Now for converting the date (in text format) to a serial number use the following formula

DATEVALUE(date_text)

For converting the DOB of the first employee(Michael) to a serial number the formula would be 

=DATEVALUE(B2)

Write the formula in Excel as shown in the screenshot

Enter the datevalue function

Now copy the formula in the entire column to convert the dates for all employees to serial numbers.

To copy the formula, click and drag the Fill Handle (plus icon) that appears when moving the cursor to the bottom-right of the selected cell. 

apply the formula to the entire column

Note that you can use this formula only when dates are in text format otherwise it will give you an error. If the dates are not in text format, you can simply change the format of the cells (as shown in Methods 1 and 2)

Also read: How to Insert Date in Excel?

Method 4: Convert Date to Serial Number Using Paste Special

In this section, we will see how you can convert the date into a serial number using the multiply option in paste special.

For demonstration purposes, I have a sample data set where I have employee names in column A and their Date of birth (DOB)  in column B. 

dataset with dates

In the above data set, DOB is in date format, Now I will show you how you can convert it into the serial number.

Below are the steps to get this done:

  1. Enter the number ‘1’ in any empty cell as shown in the screenshot
enter 1 in any empty cell
  1. Right-click on the cell containing the number 1 and choose the option copy or press CTRL + C from the keyboard
right click and copy
  1. Now select the DOB column
select the dates
  1. Right-click anywhere on the selection and choose the option Paste Special
click on paste special

This will open the Paste Special dialog box as shown in the screenshot

Paste Special dialog box
  1. Check the Multiply radio button
click on the multiply option
  1. Hit Ok
click ok

This will convert the date into a serial number as shown in the below screenshot

dates converted to serial number

Note that this method will convert the date into a serial number irrespective of whether the dates are in text or Date format.

In the above example, I used the date in text format but you can also use this method when the dates are in Date format.

All the methods covered in this tutorial will work if the dates are in the proper date format, or in the text format (but still in the format that Excel recognizes as a date). These methods would not work if the date is in a format that Excel does not recognize as a date (such as 01 01 2023 or Jan 01 2023)

Also read: Convert Days to Months in Excel

In this tutorial, I showed you various methods of converting the date into a serial number.

Remember to use either method 1 or method 2 when your dates are in any Excel-supported Date format, and use method 3 when the dates are in text format.

Similarly, Method 4 works irrespective of whether the dates are in text or Date format

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment