How to Fix Excel Online Dates and Times Displaying as Numbers
Question details
Dates and times in an Excel Online workbook are displaying as serial numbers instead of their correct date/time format, even though the formula bar shows them correctly.
- Product
- Microsoft Excel Online
- Device & OS
- not provided
- Scenario
- Editing a shared Microsoft 365 workbook in Excel for the web.
- Observed behavior
- The first rows display date and time values as raw serial numbers in the worksheet cells.
Ensure you have editing permissions for the shared Microsoft 365 workbook and verify if the issue appears only in Excel Online or also in the desktop application.
Apply the Correct Date or Time Format
Manually reapply the date or time number format to the affected cells to convert the serial numbers back to readable dates.
Excel stores dates and times as sequential serial numbers so they can be used in calculations. When the cell formatting defaults to 'General' or 'Number', these raw serial numbers are displayed.
Highlight the specific cells, rows, or columns where dates and times are displaying as serial numbers in your worksheet.
Navigate to the 'Home' tab on the Excel Online ribbon and locate the 'Number' formatting group.
Click the Number Format drop-down menu and select 'Short Date', 'Long Date', or 'Time' according to your preference.
Check for Conflicting Formats and Styles
Clear any hidden conditional formatting or pasted styles that might be overriding your standard date formats.
Format Dates and Times Easily with WPS Spreadsheet
WPS Spreadsheet offers a robust, user-friendly interface for managing data formatting. You can easily fix date and time serial number issues with full compatibility for Microsoft Excel files.
- 1. Open the file in WPS Spreadsheet: Launch WPS Office and open your .xlsx workbook containing the serial number issue.
- 2. Select the data: Highlight the cells displaying the date or time as raw numbers.
- 3. Access Format Cells: Right-click the selection, choose 'Format Cells', and navigate to the 'Number' tab.
- 4. Apply Date format: Select 'Date' or 'Time' from the category list, choose your desired format type, and click OK to apply.
Frequently Asked Questions
Why do dates show up as numbers in Excel?
Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900, is serial number 1. If the cell format is accidentally changed to 'General' or 'Number', the raw serial number is displayed instead of the formatted date.
How do I stop Excel Online from changing my date formats automatically?
This usually happens when copying and pasting data from other sources. To prevent it, use the 'Paste Special' option and select 'Values' so that the destination's formatting remains intact, or ensure there are no overriding conditional formatting rules.
Does saving a shared workbook in Excel Online affect formatting for other users?
Yes. In a shared Microsoft 365 workbook, formatting changes made in Excel Online are synced across all users. If you change a date column to a number format, it will reflect as serial numbers for everyone collaborating on the file.
