Excel has a special way of storing dates by assigning serial numbers. The serial number “1” refers to January 1, 1900, and serial dates increment by 1 for every day thereafter.
Calculations involving dates are possible because Excel treats dates as numbers. When the result we obtain is in number form, we can convert the serial number into date by following these methods:
- Change format in Format Cells
- Use TEXT function
Figure 1. Final result: Convert serial number to date
Using Format Cells to convert serial number to date
As an example, the serial number for January 1, 2019 is 43466. To convert the number 43466 into date, we follow these steps:
- Select the cell containing the serial number
- Right click and select Format Cells
Figure 2. Format Cells option in menu
- In the Format Cells dialog box, click the Number tab and select the Date category
- Choose the preferred date format in Type. The Sample box above it will show a preview of the date using the selected type.
Figure 3. Format Cells dialog box
As a result, the serial number will be converted to a date with the specific format “dd/mm/yyyy”.
Figure 4. Output: Using Format Cells to convert serial number to date
Using formula to convert serial number to date
We can also the TEXT function in a formula to convert a serial number to date.
Syntax of TEXT
=TEXT(value,format_text)
- format_text is the format code enclosed in quotation marks.
Example 1:
In cell C3, enter the formula =TEXT(B3,"mm/dd/yyyy") and press Enter.
Figure 5. Output: Entering the formula using TEXT
The serial number 43466 is instantly converted into date. Note, the resulting date is now in text format. We can also specify a different date format such as in below example.
Example 2:
In cell D4, enter the formula =TEXT(B3,"mmmm dd, yyyy") and press Enter.
Figure 6. Output: Using formula to convert serial number to date
Instant Connection to an Excel Expert
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment