This tutorial will show you how to convert a date to a number or convert a serial number to a date using Excel.
What is a Date in Excel? In Excel’s date system, dates are equal to serial numbers. For example, the first date is January 1, 1900, equivalent to 1, where 1 is an integer value. Excel increments the numbers by 1 from day to day. So, if the selected date is January 1, 2020, the serial number is 43831.
Today’s guide will show you the best practices and formulas. With its help, you can speed up your custom tasks.
Date to Number conversion
Here are the steps to convert an Excel Date to a serial number:
- Select the cell that contains a date
- Click on Home Tab, and locate the Number Group
- Select the “Number” format using the drop-down list
The conversion process can take only seconds. In the next chapter, we will look closely at what will happen if the cell contains a date, but the value is stored as text.
Date Stored as Text to Number
Sometimes you can face an issue: Excel does not recognize the date correctly and identify the date as a text. To convert a date stored as text to a number in cell B2, use the DATEVALUE function.
Enter the formula in cell D2:
=DATEVALUE(B2)
Create valid dates using the DATE function
The DATE function uses three arguments, year, month, and day. The challenge is to extract the characters from a string using custom text functions. This function can be helpful when you import data from a third-party application (for example, custom ERP systems) and Excel does not recognize the date format.
The LEFT function extracts the first 4 characters from the left, and we get the year from the text string:
=LEFT(20221231, 4) = 2022The MID function extracts the month from the text:
=MID(B3,5,2) = 12Finally, the RIGHT function strip two characters from the right:
=RIGHT(B3,2) = 31
Now we have all three arguments for the DATE function. Here is the formula to create date-stored text to valid dates:
=DATE(LEFT(B3,4),MID(B3,5,2),RIGHT(B3,2))Number to Date
You can change the cell formatting if you need to convert a serial number to a date.
To create a date from a number, use the following steps:
- Select the cell that contains a number
- Click on Home Tab, and locate the Number Group
- Select the “Date” format using the drop-down list
Additional resources:
