Four Years and a Day Off — JavaScript Bug Hunt

Briefing

Modelled on the 1900 and 1904 date systems in Microsoft Excel. Excel stores a date as a serial day number from a workbook-wide epoch: Excel for Windows uses the 1900 system, while older Excel for Mac workbooks default to the 1904 system. Microsoft's own documentation warns that copying dates between workbooks that use different systems shifts them by four years and one day (1,462 days), because the serial is pasted unchanged and reinterpreted against the other epoch.

paste.js is the clipboard handler of a spreadsheet web app that supports both systems.

Fix pasteCells so a pasted date shows the same calendar date in the target workbook.

Bug report

BUG-XL-1462 · Priority: High · Reported by: finance team (Mac + Windows)

pasteCells(cells, sourceSystem, targetSystem) returns new cells:

• a "date" cell pasted from the "1900" system into a "1904" workbook has its serial reduced by dateSystems.SYSTEM_OFFSET_DAYS (1462); the reverse direction adds it; the same system on both sides leaves it alone
• "number" and "text" cells are never changed
• the input array and its cells are not modified

After a paste, dateSystems.toIso(value, targetSystem) must equal dateSystems.toIso(original, sourceSystem).

Observed: invoice dates pasted from a Windows workbook into a Mac one show up four years and one day later.

Logs

[clipboard] paste 12 cells source=1900 target=1904
[render] A2 serial=45000 -> 2027-03-16 (source showed 2023-03-15)

The code as shipped

src/sheet/paste.js (editable)

// Pastes clipboard cells into the target workbook.
// cell: { kind: "date" | "number" | "text", value }
exports.pasteCells = function (cells, sourceSystem, targetSystem) {
  return cells.map(function (cell) {
    return { kind: cell.kind, value: cell.value };
  });
};

Read-only context: src/sheet/dateSystems.js.