How to Fix Excel Displaying Dates as Serial Numbers
Question details
The user needs to display dates correctly in their spreadsheet, as they are currently appearing as underlying serial numbers.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Viewing or formatting date cells in a worksheet.
- Observed behavior
- Cells display the underlying serial number of the date on the worksheet, while the correct date format is visible only in the formula bar.
Verify that the correct date is indeed showing in the formula bar when you select the affected cell. This confirms the data itself is intact and only the display setting needs adjustment.
Disable the 'Show Formulas' Feature
If dates suddenly turn into serial numbers across your sheet, the 'Show Formulas' view is likely active, which forces Excel to expose the underlying values.
Excel stores dates as sequential serial numbers starting from January 1, 1900. When the 'Show Formulas' mode is turned on, Excel ignores standard cell formatting and displays these underlying raw numbers.
Press the keyboard shortcut 'Ctrl + `' (the grave accent key, usually located above the Tab key) to quickly toggle the Show Formulas mode off.
Alternatively, go to the 'Formulas' tab on the top ribbon menu.
Look for the 'Formula Auditing' group and click on the 'Show Formulas' button to disable it.
Reapply Date Number Formatting
If 'Show Formulas' is not enabled, the cell format itself might have accidentally been changed to 'General' or 'Number'.
Fix Date Formatting Issues Easily with WPS Spreadsheet
WPS Spreadsheet provides intuitive formatting tools and formula auditing options to quickly resolve date display issues. Fully compatible with Microsoft Excel, it lets you format cells, toggle formula views, and manage data efficiently without any hassle.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open your spreadsheet where the dates are appearing as serial numbers.
- 2. Check the Formula Tab: Navigate to the 'Formula' tab on the top ribbon. If the 'Show Formulas' button is highlighted, click it to toggle it off.
- 3. Format as Date: If the issue persists, select your cells, right-click, choose 'Format Cells', and select 'Date' under the Number tab to restore the correct display.
Frequently Asked Questions
Why does Excel convert my dates to 5-digit numbers?
Excel stores dates internally as sequential serial numbers so they can be used in calculations. By default, January 1, 1900, is serial number 1. If the cell formatting changes to 'General' or 'Show Formulas' is enabled, Excel displays this underlying 5-digit number instead of the formatted date.
How do I fix a whole column of dates showing as numbers?
Click the column letter at the top of the grid to select the entire column. Then, go to the 'Home' tab, locate the Number formatting dropdown menu, and choose 'Short Date' or 'Long Date'. Ensure 'Show Formulas' is turned off in the Formulas tab.
Will fixing the date format alter my actual data?
No, changing the cell format only changes how the data is visually displayed on the worksheet. The underlying serial number, which represents the actual mathematical date, remains exactly the same.
