Why Are Excel Dates Five-Digit Numbers? Decoding the Serial Number Storage Trap

A Date Problem Triggered by One Converter

After processing an attendance workbook through an XLSX converter, the resulting Markdown table looked nearly perfect — except for the date column, which displayed values like 45291, 45292, 45293. The original spreadsheet clearly showed "12/31/2023, 01/01/2024, 01/02/2024" to human eyes. What the converter output were raw numbers that an AI agent simply cannot reason about.

This problem exposes a long-overlooked truth: Excel never stores dates as dates. Every date at the underlying level is a serial number — the number of days elapsed since January 1, 1900 — with the time represented as a decimal fraction. For example, 45366.5 represents noon on March 15, 2024.

In other words, the human-readable date you see in a cell is entirely determined by that cell's number format string — it might be yyyy-mm-dd, or it might be [$-409]mmmm d, yyyy, depending on the author's choice. The moment you read the raw value directly from the XML, all you get is that serial number.

More Than Just Dates: The Hidden Traps Go Deeper

The damage here goes far beyond incorrectly displayed dates — it touches the entire semantic layer of a spreadsheet's data.

Duration Misread as a Decimal

A cell with a value of 0.0833 and a format of [h]:mm actually displays as "2:00" — a duration in a column tracking hours worked. A naive converter will dump 0.0833 straight into the table, possibly even rounding it, destroying the original meaning entirely.

A Four-Year Drift from the 1904 Date System

A workbook may also use the 1904 date system (originating from legacy Mac files), which shifts every serial number by 1,462 days. A blind conversion will misplace every single date by exactly four years. This cross-platform historical quirk is one of the easiest traps to overlook and one of the most damaging.

The 1904 date system (also called the "Mac date system") originated from an early design decision in Macintosh Excel: at the time, Mac hardware couldn't represent dates before 1900, so engineers set the epoch to January 1, 1904. As a result, serial number 1 means January 1, 1900 in Windows Excel, but January 2, 1904 in the 1904 system — a difference of 1,462 days (roughly 4 years). Modern Excel allows switching between these two systems in workbook options, and the setting is saved in the XLSX file's workbook.xml (<workbookPr date1904="1"/>). A converter must explicitly read this flag to select the correct base date. When copying and pasting data across workbooks with different date systems, Excel itself will automatically adjust — which shows that even native tools have to handle this difference. Converters that rely on reading raw values are naturally even more vulnerable.

The Lotus 1-2-3 Leap Year Bug

Excel also inherited a leap year bug from Lotus 1-2-3: serial numbers below 60 are off by one day compared to the actual date. This is almost never triggered in real-world use today, but it is written into the specification — a piece of historical debt baked into the spreadsheet format.

A Reliable Approach: Check the Format First, Then Decide

The solution the author ultimately adopted is to inspect each cell's format code before deciding on any output. This logic can be distilled into three rules:

• Format contains d/m/y markers → Output ISO 8601 format (e.g., 2024-03-15), ensuring unambiguous representation regardless of the user's locale
• Format contains h/m/s markers → Render as HH:MM or ISO duration format
• Otherwise → Preserve the number as-is, because for a cell formatted as 0.00, that 0.0833 really is a measurement value, not a time

The key insight is this: the judgment is based not on the value itself, but on the semantic intent carried by the format string. Only the format can tell you whether a number is a date, a duration, or a pure measurement.

The author encapsulated this logic into their own XLSX-to-Markdown API. After processing, the attendance sheet that once output bare serial numbers now correctly produces "date": "2024-03-15" in both Markdown tables and JSON rows.

The Deeper Lesson: Spreadsheets Are a Display Format Disguised as a Data Format

This case reveals something worth serious reflection for all data engineers and AI developers: a spreadsheet is a display format disguised as a data format.

Everything you see on screen is the result of a series of rendering decisions. What the file itself stores is just numbers and format strings — some of those decisions made back in 1987 for Lotus compatibility.

This lesson is especially important now that vast amounts of Excel data are being fed into large language models and AI agents. If the conversion layer doesn't understand the separation between format and value, the LLM receives meaningless five-digit numbers it cannot reason about at all. The gap between the "rendering layer" and the "storage layer" in data pipelines is the root cause of many silent data errors.

Truly robust data conversion must understand the format alongside the value — just as a human would when reading a spreadsheet — rather than blindly reading the cold underlying number.

ISO 8601 is a date and time representation standard published by the International Organization for Standardization. Its canonical form is YYYY-MM-DD (e.g., 2024-03-15), with optional time as T14:30:00 and timezone as +08:00 or Z (UTC). It has become the preferred format for data exchange because it is completely unambiguous — 03/04/2024 might mean March 4th or April 3rd depending on the locale, but 2024-03-04 has only one interpretation. Lexicographic order also aligns with chronological order, making sorting and comparison straightforward. It is natively supported by virtually every modern data ecosystem, including JSON, XML, and databases. Converting Excel serial numbers to ISO 8601 output is the minimum requirement for enabling downstream AI models or data pipelines to correctly parse dates.