Skip to content

Opening an .xlsx turns 0 and negative date/time cells into text ("1899-12-31"), and export writes the text back #241

Closed

Description

@SuruchBoss

Found while reviewing PR #239 (#219) on a production build of b4566f9. The bug is on main before #239 too: src/lib/excelIO.ts is not touched by that PR.

What happens

A real .xlsx (written with ExcelJS) holding numbers under date/time formats, opened with Open file:

Cell Value Format Excel shows ExcelToGo shows What the cell now holds
A1 0 dd/mm/yyyy 00/01/1900 1899-12-31 text
A2 -1 dd/mm/yyyy ######## 1899-12-30 text
A3 -1.5 [h]:mm ######## 1899-12-29 12:00 text
A4 1.5 [h]:mm 36:00 36:00 number (fine)
A8 =SUM(A2:A4) [h]:mm ######## (−1) 36:00 wrong total, no warning

Because the values became text:

Values typed or computed in the app are fine after #239: a formula giving −1 under dd/mm/yyyy shows ########, and TEXT(0,"dd/mm/yyyy") is 00/01/1900. Only the import path is wrong.

Cause

ExcelJS hands a date-formatted cell over as a JS Date. rawForDate in src/lib/excelIO.ts turns that into ISO text with isoFromSerial. A serial of 0 or below becomes a year-1899 string, which dateLiteral/dateOf refuse (year < 1900), so the cell keeps the string as text. A midnight 0 under a time-only format already comes through as 00:00, and that case is fine.

Acceptance

  • A cell of 0 or a negative number under a date or time format opens as that number, with its format kept. It then shows what Show zero and negative date serials as Excel does (#219) #239 shows: 00/01/1900 for 0 and ######## for a negative.
  • Formulas reading those cells get the numbers: =A2+2 is 1, and the SUM above is −1.
  • Export writes the numbers back with their formats. Check by reading the exported file with ExcelJS.
  • A test that fails on today's main for each of: import, a formula reading the cell, and export.
  • Positive dates and times open exactly as they do today. Two to check are 45366 → 15/03/2024 and 1.5 → 36:00.

Test file used: one sheet, the values and formats in the table above, plus =A5-A5-1 and =SUM(A2:A4).

Activity

  1. added 2 commits that reference this issue on Oct 5, 2026
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions