data date_handling ai_generated true

Date calculations off by 1 day when converting Excel serial numbers, or Feb 29 1900 appears in data

ID: data/excel-date-serial-1900-bug

Also available as: JSON · Markdown
85%Fix Rate
88%Confidence
3Evidence
2023-01-01First Seen

Version Compatibility

VersionStatusIntroducedDeprecatedNotes
any active — — —

Root Cause

Excel inherited a deliberate bug from Lotus 1-2-3: it treats 1900 as a leap year (Feb 29, 1900 exists as serial number 60). This makes all serial numbers before March 1, 1900 off by 1 day. Microsoft kept this for backwards compatibility.

generic

Workarounds

  1. 92% success Use xlrd, openpyxl, or pandas to read Excel dates (they handle the 1900 bug) 
    import pandas as pd; df = pd.read_excel('file.xlsx')  # automatically handles serial date conversion
  2. 85% success If manually converting: subtract 1 from serial numbers <= 60, account for the epoch offset 
    from datetime import datetime, timedelta; date = datetime(1899, 12, 30) + timedelta(days=serial)  # note: Dec 30 not Jan 1
  3. 78% success Validate by checking for Feb 29, 1900 as a sentinel for corrupted data 
    if date == datetime(1900, 2, 29): raise ValueError('Excel 1900 leap year bug detected')

Dead Ends

Common approaches that don't work:

  1. Convert Excel serial number to date by adding days to Jan 1, 1900 82% fail

    Serial number 1 = Jan 1, 1900 in Excel, but the fake Feb 29 1900 means dates after Feb 28 1900 are correct while earlier dates are off by 1.

  2. Assume all Excel dates are correct after conversion 78% fail

    Any date before March 1, 1900 is off by 1 day. Serial number 60 maps to the non-existent Feb 29, 1900.