You may open an Excel file and find a date displayed as a number like 45321. This happens because Excel stores dates as sequential serial numbers. The program uses this system for date calculations. This article explains why this occurs and provides clear methods to reformat these numbers back into standard date formats.
Key Takeaways: Convert Excel Serial Numbers to Dates
- Home > Number Format Dropdown > Short Date: Instantly changes a 5-digit serial number to a standard date format like MM/DD/YYYY.
- Ctrl + 1 > Number tab > Date category: Opens the full Format Cells dialog for choosing from many regional date formats.
- TEXT function (e.g., =TEXT(A1,”mm/dd/yyyy”)): Creates a text version of the date for use in labels or reports, but the result is not a date for calculations.
Why Excel Displays Dates as Serial Numbers
Excel uses a date system where each day is a unique number. January 1, 1900, is serial number 1. January 2, 1900, is serial number 2, and so on. The number 45321 represents the 45,321st day after the base date, which is March 15, 2024. This system allows Excel to perform arithmetic with dates, such as calculating the number of days between two events.
A cell will show this serial number instead of a date when its formatting is set to General or Number. This often happens when importing data from other systems, when a cell’s format is accidentally changed, or when copying and pasting values. The underlying date value is still correct, but the visual presentation is a number.
The 1900 vs. 1904 Date System
Excel for Windows typically uses the 1900 date system. Excel for Mac originally used a 1904 date system, where serial number 1 is January 2, 1904. This creates a 1,462-day difference between the two systems. If you receive a file from a Mac and see dates that are exactly four years and one day off, you need to change the workbook’s date system setting in File > Options > Advanced.
Steps to Reformat Serial Numbers as Dates
The fastest way to correct the display is by changing the cell’s number format. The date value itself does not change, only how it is shown.
- Select the problem cells
Click on the cell or drag to select a range of cells showing the 5-digit numbers. - Apply a date format from the ribbon
Go to the Home tab. In the Number group, click the dropdown menu that likely says “General” or “Number.” Choose “Short Date” or “Long Date” from the list. The serial number will immediately change to a standard date.
Using the Format Cells Dialog for More Control
For more format options, use the full Format Cells dialog.
- Open the Format Cells dialog
Select your cells and press Ctrl + 1. You can also right-click the selected cells and choose Format Cells. - Choose the Date category
In the dialog box, click the Number tab. Select “Date” from the Category list on the left. - Select your preferred format
The right side of the dialog shows various date formats. Choose one like “3/14/2012” or “Wednesday, March 14, 2012.” The Locale setting lets you pick regional formats. Click OK to apply.
Converting with the TEXT Function
Use the TEXT function when you need a date formatted as text for a report header or a combined label. The result will look like a date but cannot be used in date calculations.
- Enter the TEXT function formula
In a new cell, type a formula like =TEXT(A1, “mm/dd/yyyy”). Replace A1 with the cell containing the serial number. - Use a different format code
Change the format code in quotes to display the date differently. For example, =TEXT(A1, “dd-mmm-yy”) would display “15-Mar-24”.
Common Mistakes and Things to Avoid
Dates Remain Numbers After Formatting
If applying a date format does not work, the cell might contain text that looks like a number, not a true numeric value. Check by aligning the cell to the left; numbers align right by default. To fix this, use the VALUE function. In a new column, use =VALUE(A1). This converts text to a number, which you can then format as a date.
Pasting Values Loses Date Formatting
When you copy a date and use Paste Special > Values, you paste only the underlying serial number, not the format. The cell reverts to General format, showing the 5-digit number. Always paste formatting as well, or reapply the date format after pasting values.
Using Formulas that Return Numbers
Some functions, like DATEVALUE, return a serial number. If the cell is formatted as General, it will display that number. The solution is to wrap the function in a TEXT function or format the result cell as a date immediately after entering the formula.
Manual Conversion vs. Formatting: Key Differences
| Item | Changing Cell Format | Using TEXT Function |
|---|---|---|
| Underlying Value | Remains a usable serial number for calculations | Converts to unchangeable text |
| Best For | Correcting display for dates used in formulas and sorting | Creating fixed labels, headers, or text strings |
| Permanence | Format can be changed again easily | Result is static text; original number remains separate |
| Cell Reference | Works directly on the original cell | Requires a formula in a different cell |
You can now identify and fix cells where Excel shows a date as a 5-digit serial number. The primary tool is the Number Format dropdown on the Home tab. For complex scenarios, use the Format Cells dialog accessed with Ctrl + 1. Remember that the TEXT function is for display only, not for calculations. To prevent future issues, be careful when using Paste Special > Values, as it strips away number formatting.