← Back to Error Directory

Dates Showing as Numbers Instead of Dates? Complete Fix

That five-digit number isn't broken data — it's the correct value, just displayed in the wrong format. Here's the fix and the logic behind it.

Table of Contents

1. How Excel Actually Stores Dates Internally

Excel has no separate internal "date" data type — every date is stored as an ordinary number, counting the days elapsed since a fixed starting point (January 1, 1900, which is stored as serial number 1). What makes a date look like a date rather than a plain number is purely the cell's display format — the underlying value never changes, only how it's presented.

Time values work the same way, stored as the fractional portion of that same serial number — 12:00 PM (noon) is represented internally as exactly .5, since it's precisely halfway through a day.

2. Interactive Demo: Serial Number Converter

See the exact relationship between a date and its underlying serial number.

3. The Quick Fix: Format Cells

  1. Select the cell(s) showing the raw serial number.
  2. Press Ctrl+1 to open Format Cells.
  3. Under the Number tab, select Date from the category list.
  4. Choose your preferred date display format (e.g., MM/DD/YYYY, DD-MMM-YYYY) and click OK.

Crucially, this doesn't change the actual value stored in the cell at all — it only changes how that same underlying number is displayed. This is why formatting a cell as Date never affects any calculation that references it; the math continues working on the same serial number regardless of display format.

4. Why Date Formulas Lose Their Formatting

A frequent point of confusion: a formula that calculates or references a date sometimes displays the raw serial number even when you expected a formatted date to carry through automatically. This typically happens with:

Fix: manually reapply Date format (or Number format, if you genuinely want a day-count rather than a date) to the result cell using the same Format Cells method above.

5. Text Dates vs True Date Serials

Not every date-looking value in Excel is actually a true date serial number — dates imported from certain systems, or typed in unusual formats Excel doesn't recognize, can be stored as plain text that merely resembles a date. These will be left-aligned (the tell-tale sign of text, same as covered in our formula-as-text guide) rather than right-aligned like a genuine date serial. To convert genuine text-formatted dates into true date serials:

=DATEVALUE(A2)

This converts a text string that represents a date into the corresponding true serial number, which you can then format as a Date normally.

6. The 1900 vs 1904 Date System Difference

Excel actually supports two different date systems, a legacy holdover from compatibility with old Macintosh Excel versions. The default (1900 system) starts counting from January 1, 1900. The older Mac-compatible option (1904 system) starts counting from January 1, 1904 instead. If a workbook was created or edited using the 1904 system and is later opened somewhere expecting the 1900 system, every date can appear shifted by exactly four years — check File → Options → Advanced → When calculating this workbook → Use 1904 date system if you encounter unexplained multi-year date discrepancies between file versions.

7. Negative Serial Numbers and Pre-1900 Dates

Because the 1900 system's serial numbering starts at 1, any calculation that would produce a date before January 1, 1900 results in a negative or zero serial number, which Excel cannot display as a valid date at all — this is one of the root causes covered in our #NUM! error guide under date-related calculations.

8. Google Sheets Date Serial Behavior

Google Sheets uses the same fundamental concept — dates stored as serial numbers — but with a slightly different epoch, counting from December 30, 1899 rather than January 1, 1900 (a quirk related to how each platform historically handled a leap-year calculation bug from the original Lotus 1-2-3 spreadsheet software). In practice, this rarely causes visible problems since both platforms handle their own internal conversions consistently, but it's worth knowing if you're writing a script or API integration that needs to convert raw serial numbers between the two platforms manually.

9. Frequently Asked Questions

Why does Excel show a date as a number like 45392?

Excel stores every date internally as a serial number counting days since January 1, 1900. When a cell containing this serial number is formatted as General or Number instead of Date, you see the raw underlying number rather than a readable date.

How do I convert a date serial number back into a readable date?

Select the cell, open the Format Cells dialog with Ctrl+1, go to the Number tab, and choose Date, then pick your preferred display format. The underlying value does not change, only how it is displayed.

Why did my date formula result lose its date formatting?

Certain operations, such as subtracting two dates or referencing a date cell inside an arithmetic formula, can cause Excel to default the result cell back to General format, which displays the underlying serial number instead of automatically reapplying date formatting.

Related Guides

Editorial Disclaimer: This guide is developed to the best of our domain knowledge and tested against Excel 2019+, Microsoft 365, and Google Sheets. Content is updated continuously as spreadsheet calculation engines evolve.