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.
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:
- 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.
- Click on the Home tab in the ribbon
- In the Number section, click on the Number Format dropdown
- From the drop-down select the Number option
This will convert all the selected dates to serial numbers as shown in the below screenshot.
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.
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:
- Select the date that you want to convert into the serial number. For this example, I am going to select the DOB column.
- Click on the Home tab in the ribbon
- Click on the Format option
- From the drop-down that shows up click on the option Format Cells
- This will open the Format Cell dialog box as shown below
- Select the Number option in the Number tab
- [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.
- [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.
- Click on OK
This will convert the selected date into a serial number as shown in the below screenshot
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.
As shown in the screenshot below, the dates in Column B are in text format (see the highlighted format in the ribbon)
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
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.
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.
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:
- Enter the number ‘1’ in any empty cell as shown in the screenshot
- Right-click on the cell containing the number 1 and choose the option copy or press CTRL + C from the keyboard
- Now select the DOB column
- Right-click anywhere on the selection and choose the option Paste Special
This will open the Paste Special dialog box as shown in the screenshot
- Check the Multiply radio button
- Hit Ok
This will convert the date into a serial number as shown in the below screenshot
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:
- How to Remove Year from Date in Excel?
- How to Convert Text to Date in Excel?
- Convert YYYYMMDD to MM/DD/YYYY in Excel
- How To Combine Date and Time in Excel
- How to Separate Date and Time in Excel
- How to Get the First Day Of The Month In Excel?
- Why are Dates Shown as Hashtags in Excel? Easy Fix!
- How to Add Minutes to Time in Excel?
- How to Apply Long Date Format in Excel?