'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix Excel Dates Displayed as Five-Digit Numbers
Question details
The user needs to display invoice dates properly, but they appear as five-digit serial numbers and standard date formatting does not resolve the issue.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Viewing or formatting invoice dates in an Excel spreadsheet.
- Observed behavior
- Dates appear as 5-digit numbers (e.g., beginning with 45) despite date formatting being applied to the cells.
Verify that your cells are actually formatted as Dates by highlighting them and checking the Number Format dropdown in the Home tab.
Disable the Show Formulas Feature
Turning off the 'Show Formulas' auditing mode resolves the issue where Excel forces the display of underlying serial values instead of formatted dates.
Excel stores dates as sequential numbers to allow for calculations. When the 'Show Formulas' mode is accidentally enabled, Excel reveals these underlying values (like 45000) instead of applying your date format. Disabling this mode restores normal display.
Open your Excel workbook and click on the 'Formulas' tab located in the top ribbon.
Find the 'Formula Auditing' group within the Formulas tab menu.
Check if the 'Show Formulas' button is highlighted. If it is enabled, click it once to turn it off. Your serial numbers will immediately display as formatted dates.
Fix Date Formatting Issues Seamlessly in WPS Spreadsheet
WPS Spreadsheet handles date formatting, formula auditing, and large datasets flawlessly. It offers an intuitive interface where toggling formula views and adjusting cell formats is quick, easy, and completely free.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the document containing the date display issue.
- 2. Navigate to the Formulas tab: Click on 'Formulas' in the top menu ribbon.
- 3. Disable Show Formulas: Click on the 'Show Formulas' icon in the Formula Auditing section to turn it off and restore normal date formatting.
Frequently Asked Questions
Why does Excel store dates as five-digit numbers?
Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900, is serial number 1. Dates in the 2020s typically start with 44 or 45.
What if turning off 'Show Formulas' doesn't fix my date display?
If disabling 'Show Formulas' doesn't work, select the cells, navigate to the Home tab, and ensure the Number Format dropdown is explicitly set to 'Short Date' or 'Long Date' rather than 'Text' or 'General'.
How do I convert text dates to serial numbers in Excel?
If dates are stored as text and won't format properly, you can convert them by selecting the cells, going to Data > Text to Columns, and immediately clicking Finish. Alternatively, use the DATEVALUE function.
Can this issue happen when copying and pasting data?
Yes, if you paste date data into a cell that is pre-formatted as General or Number, it may display the serial number. Simply changing the cell format back to Date will fix it, provided 'Show Formulas' is off.
