🍱 Lunchbox Hands

excel

Why Excel Dates and IDs Break When You Export Them (45292 Explained)

The real mechanism behind Excel turning dates into numbers like 45292, stripping leading zeros from IDs, and mangling long numbers into scientific notation — with the fixes.

A date in a spreadsheet cell isn’t a date. It’s a number wearing a costume. Export that cell to CSV or JSON and the costume comes off — you get 45292 instead of a day, or a 16-digit order number turns into 1.23457E+15. Neither is a bug in the strict sense. Both are the predictable result of how Excel stores values internally, and once you see the mechanism, the “wrong” exports stop being mysterious.

Dates are numbers with a mask on top

Excel doesn’t store a date as a date. It stores a serial number — a count of days — and a number format that tells the cell how to display that number. The value and the display are two completely separate things living in the same cell.

The count starts at day 1 for January 1, 1900. So:

Serial 1     = January 1, 1900
Serial 45292 = January 1, 2024

You can verify that second one by hand. From January 1, 1900 to January 1, 2024 is 124 years: 94 common years plus 30 real leap years (every 4th year, minus the century rule — 1900 itself isn’t one, more on that below), giving 94 × 365 + 30 × 366 = 34,310 + 10,980 = 45,290 real elapsed days. Add Excel’s own phantom February 29, 1900 — a day the calendar never had but Excel’s serial count includes anyway (see the next section) — and you get 45,291 elapsed days after day 1, which lands on serial 45,292. Change the cell’s number format from “Date” to “General” or “Number” and Excel shows you that raw integer immediately — the stored value never changed, only the mask did.

This is why a naive export — one that reads the raw cell value without checking the format — hands you 45292 instead of 2024-01-01. The date was never “converted” to a number by the export; the number was there the whole time.

Time-of-day works the same way, as the fractional part of the serial. 45292.5 is noon on January 1, 2024. 45292.75 is 6 PM.

The 1900 leap year bug

Here’s where it gets genuinely strange: Excel believes 1900 was a leap year. It wasn’t. Microsoft’s own support documentation confirms this is deliberate, not a defect they consider worth fixing, and explains why: Lotus 1-2-3, the dominant spreadsheet before Excel, made the same wrong assumption first. When Excel entered the market, matching Lotus’s serial numbers exactly — including its bug — meant worksheets moved between the two programs without every date shifting by a day. Excel copied the mistake on purpose, for compatibility.

Concretely, Excel’s serial number 60 is labeled “February 29, 1900” — a date that never existed. (1900 is a century year, and century years are only leap years if divisible by 400; 1900 isn’t, so the real February 1900 had 28 days.) Microsoft’s documentation is explicit that fixing this now would be worse than leaving it: “almost all dates in current Microsoft Excel worksheets and other documents would be decreased by one day” if they patched it, breaking every existing workbook and formula in the world to correct a phantom day nobody notices. They also note the one place it’s not invisible: the WEEKDAY() function returns incorrect values for real dates before March 1, 1900, because Excel’s internal day count includes that extra, non-existent day.

The practical consequence for anyone writing an export or import routine: if you convert an Excel serial number to a real calendar date using ordinary day arithmetic — no special case for the phantom day — every date from March 1, 1900 onward comes out one day too late, because your arithmetic is correctly skipping a day that Excel’s serial count didn’t actually skip. This is why every competent Excel library (SheetJS, openpyxl, and the Excel → JSON tool on this site included) hard-codes a special case around serial 60 rather than treating the 1900 system as a clean day count. Get it wrong and every single date in the file is off by one — a bug that’s easy to ship because it “mostly” looks right.

Two epochs: 1900 vs. 1904

There’s a second, unrelated place dates can shift: Excel actually supports two different date systems, and which one a workbook uses is stored as a per-file setting, not a global default.

  • The 1900 date system counts from January 1, 1900 (serial 1) and is the default on Windows.
  • The 1904 date system counts from January 1, 1904 (serial 0) and was the historical default for Excel on classic Mac OS, because early Macintosh file systems couldn’t represent dates before 1904.

Microsoft documents the difference precisely: the two systems are offset by exactly 1,462 days for the same calendar date — that’s 4 years plus the leap day Excel inserted into 1900. Open a file authored under one system in software that assumes the other, and every date in the sheet silently shifts by almost exactly 4 years. Modern Excel for Mac defaults to the 1900 system too, but old files — and some Numbers or LibreOffice exports — can still carry the 1904 flag.

You can check which system a workbook uses in Excel itself: File → Options → Advanced → scroll to “When calculating this workbook” → look at the “Use 1904 date system” checkbox. A properly built import tool reads this flag from the workbook’s own metadata rather than assuming — guessing wrong silently shifts every date by four years and a day, with no error to warn you.

Timezones: the serial number doesn’t have one

A date-time serial number is just a count of days (and fractions of a day) from an epoch — it carries no timezone information at all. 45292.5 means “noon, on the serial clock,” full stop. It isn’t UTC noon or local noon; those concepts don’t exist at the storage layer.

The danger shows up at export time. If your conversion code takes that serial number, builds a Date object using a local-time constructor, and then calls something like .toISOString() (which always outputs UTC), you’ve silently applied a timezone shift that was never in the original data. A spreadsheet timestamp of “9:00 AM” typed by someone in New York can come out as 13:00Z or 14:00Z in your JSON depending on the server’s local timezone and daylight saving — not because the data changed, but because your conversion code guessed a timezone the spreadsheet never had.

The fix is to treat the serial number as timezone-naive and construct the date using UTC-based methods the entire way through — never route it through a local-time constructor. That keeps “9:00 AM” as 09:00:00.000Z in the output: a faithful re-encoding of what was in the cell, not a shifted guess. (For the equivalent problem on the other side — Unix timestamps, which are always UTC by definition — see Unix Timestamps and Epoch Time Explained.)

The identifier problem: precision and leading zeros

Dates aren’t the only casualty. Excel’s number handling causes two more failure modes for anything that looks like a number but isn’t meant to be treated as one:

Leading zeros disappear. A zip code of 00501 or a SKU of 007123 typed into a “General”-formatted cell is auto-detected as a number and stored as 501 or 7123 — the leading zeros were never significant to a number, so Excel drops them the moment it decides the cell is numeric. There’s no “undo” once the file is saved; the zeros are gone from the stored value, not just the display. (Excel is only the most famous offender — any format that guesses types from the text hits the same wall, which is why INI-to-JSON converters have to refuse to coerce 007.)

Long numbers lose precision. Excel stores all numbers — dates included — as IEEE 754 double-precision floats, and Microsoft’s own documentation confirms Excel keeps only 15 significant digits of precision, silently zeroing out anything past that. A 16-digit credit-card-style number, an IMEI, or a long order ID typed as a number gets its trailing digits truncated to zero — 1234567890123456 becomes 1234567890123450, and there is no way to recover the original digits once that happens. On top of the truncation, Excel’s default “General” format renders anything wide enough as scientific notation, so the cell shows 1.23457E+15 instead of the number at all.

Both problems have the same root cause and the same fix: numeric-looking identifiers are not numbers. They don’t participate in arithmetic, they can start with zero, and they can be longer than 15 digits. They belong in text-formatted cells, full stop.

This isn’t a hypothetical concern for niche cases — it broke published science. In 2020, the HUGO Gene Nomenclature Committee, the body that assigns official names to human genes, renamed several genes specifically because their existing symbols were being silently converted to dates by Excel’s autocorrect. MARCH1 (Membrane-Associated RING-CH protein 1) became MARCHF1; SEPT1 became SEPTIN1 — because typing MARCH1 or SEPT1 into a cell got auto-formatted to 1-Mar or 1-Sep, corrupting the gene symbol in any spreadsheet where it appeared. The renaming guidelines were published in Nature Genetics in August 2020. It’s the clearest real-world proof that “just a spreadsheet quirk” can propagate into published, peer-reviewed data.

Text vs. date cells in the same column

It’s common to open a spreadsheet and find one column where most rows hold a genuine date value and a handful hold plain text that merely looks like a date — "03/04/2026" as a string, not a date serial. This happens more easily than it should:

  • Pasting data from a web page, PDF, or another app pastes as text by default; Excel only auto-converts it to a date if the format matches a pattern it recognizes, and not every format does.
  • CSV imports have no cell-format metadata at all — Excel has to guess whether each cell is a date, a number, or text, column by column, and it guesses inconsistently between rows if the formats within a column vary.
  • Locale settings change what Excel considers a valid date pattern. A date string that auto-converts under a US locale (3/4/2026) might stay text under a locale that doesn’t recognize month-first ordering, or vice versa.

The result: a column that looks uniform in the Excel UI is actually a mix of real date serials and plain strings under the hood. Export that column and you get mixed types in your output — some rows produce a proper ISO date, others produce whatever raw text was in the cell — and any downstream code that assumes one consistent type breaks on the rows it didn’t expect.

Locale ambiguity: there is no “the” date format

Even when a cell unambiguously holds a real date, the string representation you choose to export it as can still be wrong for someone. 03/04/2026 is March 4th to a US reader and April 3rd to almost everyone else. There is no way to guess correctly from the string alone — you have to know the convention the writer used.

The only format that isn’t ambiguous is ISO 8601: 2026-04-03. Year first, then month, then day, zero-padded, hyphen-separated. No reader anywhere reads that as anything other than April 3, 2026, because the format itself declares its own field order. If you’re exporting dates for another system to consume — an API, a database, a script in a different country — ISO 8601 is the only interchange format that doesn’t require an out-of-band agreement about what the digits mean.

What you see vs. what’s stored vs. what you get

What the cell showsWhat’s actually storedWhat lands in your JSON (naive export)
1/1/2024Serial number 4529245292
2/29/1900Serial number 60 (a date that never existed)60, or a parse error, or the wrong real date
00501Number 501 (zeros already gone)501
41234567890123454123456789012340 (rounded to 15 sig. figs)4123456789012340 or 4.12346E+15
03/04/2026 (US-authored)Serial for March 4, 20262026-03-04 (correct) or 03/04/2026 (ambiguous) depending on the exporter
9:00 AM typed by a New York userSerial fraction 0.375 (no timezone)13:00Z or 14:00Z — wrong, if the exporter guessed a local timezone

A safe-export checklist

  1. Format date columns as ISO before exporting. Set the cell format to YYYY-MM-DD (or YYYY-MM-DDTHH:MM:SSZ for date-times) so the displayed value is already unambiguous, rather than relying on the export step to reformat correctly.
  2. Keep identifiers as text, not numbers. Format ID/zip/SKU columns as Text before typing or pasting data into them — converting after the fact doesn’t restore zeros or digits that are already gone. If a system upstream forces numeric entry, prefix with a non-numeric character for transport and strip it on the other side.
  3. Export dates as ISO 8601 strings, never raw serials. A serial number like 45292 means nothing without knowing the file’s date system (1900 vs. 1904) and the exporter’s timezone assumptions. A string carries its own meaning.
  4. Verify a sample row after conversion. Pick a date near a DST boundary, a date before 1901, and an ID with leading zeros or 16+ digits, and manually check that all three survived the round trip.
  5. Prefer a converter that reads the workbook’s own date-system metadata — the 1900/1904 flag — rather than one that assumes the Windows default. A converter that guesses will be right most of the time and catastrophically wrong on the files that don’t match the assumption.

Tools that handle this correctly

The Excel → JSON / CSV converter on this site reads XLSX, XLS, and ODS files entirely in your browser — the file never uploads anywhere — and converts real date cells to ISO 8601 strings (2026-01-15T00:00:00.000Z) rather than handing you raw serial numbers. It handles multi-sheet workbooks, lets you choose whether the first row is headers, and outputs either JSON or CSV.

A few related tools if the data’s already past the spreadsheet stage:

The short version: a spreadsheet cell is a number with a costume, the costume can come from either of two different epochs, and identifiers that look like numbers should never be treated as numbers. Export with all three in mind and 45292 never makes it into your JSON again.