Cursed Excel: "1/2"+1=45660

Original Article ↗ Hacker News Discussion ↗

Date parsing, locales, and the “45660/45690” result

  • Different commenters get different results for "1/2"+1 depending on:
    • Locale (MM/DD vs DD/MM vs ISO),
    • Application (Excel vs Google Sheets vs LibreOffice),
    • Whether the cell is treated as text or date.
  • General consensus: spreadsheets aggressively interpret 1/2 as a date (e.g., Jan 2 or Feb 1) and then add a day, returning a serial date number (e.g., 45660).
  • Several people argue ISO (YYYY‑MM‑DD) is the only sane ordering; others note many languages and cultures use “little‑endian” dates and even non‑decimal number systems.

Automatic conversions and new Excel setting

  • Many complain that Excel’s automatic date parsing has caused more grief than any other feature.
  • People highlight that Excel also auto-converts numeric-looking strings (e.g., long IDs) into numbers, sometimes truncating or mangling them.
  • A relatively new Excel option (“Automatic Data Conversions”) can disable some of this, but:
    • It only affects data entry/CSV import, not values already stored.
    • It’s per-user, not embedded in CSV; others opening the same file may still get mangling.
    • At least one commenter reports that even with options unticked, typing 1/2 still becomes a date.

Data integrity problems (genes, ZIP codes, IDs)

  • Examples raised:
    • Gene names auto-converted to dates, forcing the scientific community to rename some genes.
    • US ZIP codes losing leading zeros.
    • Phone numbers and identifiers showing up in exponential notation.
  • Debate over whether leading zeros are “extraneous”; several insist any user-entered zeros should be preserved unless the user explicitly chooses otherwise.

Calendars, historical dates, and leap-year quirks

  • Discussion of Gregorian vs Julian transitions (1582, 1752, ~1920) and how real-world history complicates any “true” date system.
  • Some note that most software simply projects the modern Gregorian calendar backward.
  • Excel doesn’t support dates before 1900 and historically inherited the “1900 is a leap year” bug from Lotus 1‑2‑3 for compatibility.

User intent vs “smart” software

  • Strong sentiment against software “guessing” user intent (“Did you mean…?”).
  • Others argue that in business use, 1/2 is far more often a date than a fraction, so Excel’s default is pragmatic for its main audience.
  • Suggestions: treat ambiguous inputs as literal strings, or only coerce when the operation demands it, possibly with warnings.

Localization, internals, and alternatives

  • Debate over whether XLSX stores formulas with invariant English function names or localized ones; no clear consensus in the thread.
  • Some prefer databases, Jupyter, or array languages for serious data work, then export to Excel only for visualization.
  • Multiple commenters note the article doubles as an ad for a new “AI spreadsheet,” and some are skeptical that it will avoid similar traps.