How to Fix Excel Showing Date Serial Numbers Instead of Dates
Question details
The user needs to convert Excel serial numbers back into a readable calendar date format when referencing date cells in a new location.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Referencing a date cell in a formula or importing data where the destination cell defaults to a General or Number format.
- Observed behavior
- Excel displays a 5-digit serial number (based on the 1900 date system) instead of the expected calendar date.
Select the cells containing the unexpected serial numbers and ensure they do not have leading apostrophes, which would force them to be treated as plain text rather than numeric dates.
Apply Date Formatting to the Destination Cells
The most direct way to fix serial numbers is to change the cell format from General to Date.
Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1. When you reference a date cell in another part of your sheet, the destination cell might retain a 'General' format, revealing this underlying number instead of the formatted date.
Highlight all the cells that are currently displaying the 5-digit serial numbers.
Navigate to the 'Home' tab on the top ribbon and locate the 'Number' group.
Click the dropdown menu (which likely says 'General') and select 'Short Date' or 'Long Date'.
Use the TEXT Function for Automatic Formatting
If you want the date to always display correctly regardless of the cell's default formatting, convert it to a formatted text string within your formula.
Fix Date Serial Numbers Instantly with WPS Spreadsheet
WPS Spreadsheet provides intuitive cell formatting options and full support for advanced Excel functions like TEXT, allowing you to manage and format dates effortlessly.
- 1. Open your file: Launch WPS Spreadsheet and open the document containing the date formatting issues.
- 2. Select the cells: Highlight the cells currently showing the 5-digit serial numbers.
- 3. Format as Date: Right-click and select 'Format Cells', navigate to the 'Number' tab, click 'Date', and pick your preferred calendar layout.
Frequently Asked Questions
Why does Excel convert my dates to 5-digit numbers?
Excel stores all dates as sequential serial numbers starting from January 1, 1900 (which is number 1). A number like 45200 represents the number of days since that starting point. This system allows Excel to easily add or subtract days in mathematical formulas.
Can I make Excel automatically display the normal date format without manually formatting?
Excel's default behavior is to apply 'General' formatting to empty cells. To force a date to display automatically without touching the cell format settings, you must use a formula like =TEXT(A1, "yyyy-mm-dd"), which converts the serial number into formatted text.
Why do dates look different when I import my file into Google Sheets?
Different spreadsheet applications handle default cell formatting in their own ways. While the underlying 5-digit serial number remains identical across platforms, the importing application may interpret the 'General' cell format strictly as a standard number rather than auto-detecting it as a date.
How do I fix serial numbers when exporting data from another application?
If you are generating Excel files from a programming language (like Ruby on Rails) or exporting from a database, the raw output is often just the serial number. You must explicitly apply an Excel Date number format to the destination cells within your code's export logic, or export the dates as pre-formatted text strings.
