Handle the 1904 Date System in Excel Files with Python

Excel stores a date as a number of days since an origin. Almost every workbook uses the 1900 system, in which 46082 is 1 March 2026. A minority — historically, workbooks created on early Macs, and any workbook where someone ticked "Use 1904 date system" — count from 1 January 1904 instead, and there the same date is 44620. As long as a cell is formatted as a date, Excel and Python libraries convert it correctly. When serial numbers travel as plain numbers — an unformatted column, a CSV export, values pasted between workbooks — they arrive exactly four years and one day wrong. This page, part of Working with Dates and Times in Excel Data, shows how to detect the system and convert correctly.

Prerequisites

pip install openpyxl pandas

windows.xlsx and mac1904.xlsx each contain one order shipped on 1 March 2026, stored as a date-formatted cell, and a due_serial column holding the same date as a plain, unformatted serial number — 46082 in the 1900 workbook, 44620 in the 1904 one. That second column is the kind of thing an export or a formula produces.

Step 1 — Detect the date system

openpyxl reads the workbook's date1904 flag and exposes it as wb.epoch:

from openpyxl import load_workbook
from openpyxl.utils.datetime import CALENDAR_MAC_1904

def date_system(path):
    wb = load_workbook(path, read_only=True)
    try:
        return "1904" if wb.epoch == CALENDAR_MAC_1904 else "1900"
    finally:
        wb.close()

for name in ("windows.xlsx", "mac1904.xlsx"):
    print(name, date_system(name))

In the file itself the setting is one attribute on the workbook part, <workbookPr date1904="1"/>, so a zip-level check works too when openpyxl is not available. Checking is cheap enough to do for every file a job reads, and far cheaper than finding the problem in a finished report.

Step 2 — See that date-formatted cells are already right

For cells formatted as dates, the libraries apply the right origin automatically:

import pandas as pd

for name in ("windows.xlsx", "mac1904.xlsx"):
    print(name, pd.read_excel(name).iloc[0]["shipped"], "| calamine:",
          pd.read_excel(name, engine="calamine").iloc[0]["shipped"])

Both files give 1 March 2026 with both the openpyxl and the calamine engines. If all the dates you care about are formatted cells, a 1904 workbook needs no special handling at all. The rest of this page is about the numbers that are not formatted.

Step 3 — Convert raw serial numbers with the right origin

An unformatted serial is just a number to every reader, and converting it requires knowing which origin it counts from. With the wrong one, the date is off by 1,462 days:

mac = pd.read_excel("mac1904.xlsx")
serial = mac.loc[0, "due_serial"]
print(serial)
print("as 1900:", pd.to_datetime(serial, unit="D", origin="1899-12-30").date())
print("as 1904:", pd.to_datetime(serial, unit="D", origin="1904-01-01").date())

Read as a 1900 serial, 44620 is 28 February 2022 — four years and one day early. With the 1904 origin it is 1 March 2026, matching the formatted column. The 1900 system's origin is written as 30 December 1899, not 1 January 1900, because Excel's 1900 system counts a nonexistent 29 February 1900; using the 30th makes every date from March 1900 onwards come out right.

Step 4 — Convert serial columns using each file's own system

When a job reads many files, pick the origin per file from Step 1 rather than assuming. A small helper keeps the rule in one place:

ORIGINS = {"1900": "1899-12-30", "1904": "1904-01-01"}

def read_with_serial_dates(path, serial_columns):
    frame = pd.read_excel(path)
    origin = ORIGINS[date_system(path)]
    for col in serial_columns:
        frame[col] = pd.to_datetime(frame[col], unit="D", origin=origin)
    frame["source"] = path
    return frame

combined = pd.concat([read_with_serial_dates(p, ["due_serial"]) for p in ("windows.xlsx", "mac1904.xlsx")])
print(combined[["source", "shipped", "due_serial"]].to_string(index=False))

Both rows now show 1 March 2026 in both columns, although the underlying serials were 46082 and 44620. Without the per-file origin, concatenating the two would silently mix dates four years apart — the kind of error that survives until someone notices a shipment "due" before it was ordered.

Step 5 — Write a workbook in either system

openpyxl writes the 1900 system by default. Set wb.epoch before writing to produce a 1904 workbook, for example to match a template that downstream users expect:

import datetime as dt
from openpyxl import Workbook

wb = Workbook()
wb.epoch = CALENDAR_MAC_1904
ws = wb.active
ws["A1"] = dt.datetime(2026, 3, 1)
ws["A1"].number_format = "yyyy-mm-dd"
wb.save("written_1904.xlsx")

import re, zipfile
with zipfile.ZipFile("written_1904.xlsx") as z:
    print(re.findall(r"<workbookPr[^>]*>", z.read("xl/workbook.xml").decode()))
    print(re.findall(r'<c r="A1"[^>]*><v>(\d+)</v>', z.read("xl/worksheets/sheet1.xml").decode()))

The file carries date1904="1" and stores the date as 44620. Assign Python datetime objects rather than serial numbers when writing — openpyxl converts them with the workbook's epoch, so they are correct whichever system you chose. Writing precomputed serials is where mistakes creep in.

Convert a 1904 workbook to the 1900 system

When a 1904 workbook should join the rest of an organisation's files on the 1900 system, openpyxl can convert it: date cells are loaded as Python datetimes, and saving with the other epoch re-encodes them. Plain serial columns are just numbers to openpyxl, so they must be shifted by hand:

from openpyxl.utils.datetime import CALENDAR_WINDOWS_1900

wb = load_workbook("mac1904.xlsx")
wb.epoch = CALENDAR_WINDOWS_1900
ws = wb.active
for (cell,) in ws.iter_rows(min_row=2, min_col=3, max_col=3):   # the unformatted serial column
    if isinstance(cell.value, (int, float)):
        cell.value += 1462
wb.save("converted_1900.xlsx")

print(date_system("converted_1900.xlsx"), pd.read_excel("converted_1900.xlsx").iloc[0].to_dict())

The shipped date stays 1 March 2026 without any code touching it, and the serial becomes 46082, the 1900-system number for the same day. Know which columns hold serials before converting — shifting a quantity column by 1,462 would be an interesting way to inflate stock — and convert a copy, keeping the original for comparison.

Step 6 — Check a folder for mixed systems

Before consolidating a folder of workbooks, find out whether any use the 1904 system. It is rare enough that nobody expects it, which is exactly why it is worth checking:

from pathlib import Path

systems = {p.name: date_system(p) for p in sorted(Path(".").glob("*.xlsx"))}
print(systems)
print("1904 files:", [n for n, s in systems.items() if s == "1904"])

Log the result in the job. If a 1904 file appears where none has before, it usually means someone rebuilt a workbook from an old template — a detail worth knowing before their dates are merged into a report.

Common pitfalls

• Assuming every file is 1900-based. Check wb.epoch for files you did not create.
• Using 1900-01-01 as the 1900 origin. It gives dates two days off; use 1899-12-30 with unit="D".
• Writing serial numbers instead of datetimes. Let openpyxl convert with the workbook's epoch.
• Serial numbers typed into formulas. A condition such as =B2>45000 means a different date in each system. Write DATE(2026,3,1) in formulas instead, which each workbook evaluates in its own system.
• Exporting serials to CSV. Once a serial leaves the workbook, nothing records which origin it used; convert to ISO dates before exporting.
• Pasting between workbooks. Excel itself shifts pasted date values when the two workbooks use different systems; convert through Python datetimes instead.

Performance notes

date_system opens each workbook in read-only mode and reads only the workbook part, so checking hundreds of files takes a second or two. Serial conversion with to_datetime(unit="D") is vectorised and effectively free.

Conclusion

A 1904-system workbook stores the same date as a serial number 1,462 lower. Date-formatted cells are converted correctly by openpyxl and pandas either way; raw serials are not. Detect the system per file with wb.epoch, convert unformatted serials with origin 1899-12-30 or 1904-01-01 to match the source, keep the origin per file when combining several, and write dates as Python datetimes so the workbook's own epoch is applied.

Frequently asked questions

What is the 1904 date system? An alternative way for a workbook to store dates, counting days from 1 January 1904 instead of from the end of 1899. It came from early Macintosh versions of Excel and is still a per-workbook setting today. The same serial number is 1,462 days later in a 1904 workbook.

Do pandas and openpyxl handle 1904 workbooks correctly? Yes, for cells formatted as dates. openpyxl reads the workbook's date1904 flag and converts date cells with the right origin, and pandas inherits that. Problems appear only with raw serial numbers that are not formatted as dates.

How do I tell which system a file uses? Load it with openpyxl and check wb.epoch: 1899-12-30 means the 1900 system, 1904-01-01 means the 1904 system. In the file itself, the workbook part has a date1904="1" attribute when the 1904 system is on.

Why are my dates exactly four years and one day off? Serial numbers from one system were read with the other system's origin. The two origins are 1,462 days apart. Convert serials with the origin of the workbook they came from.

Related

• Up one level: Working with Dates and Times in Excel Data — dates from import to report.
• Fix Excel Serial Numbers Showing Instead of Dates — serials in the 1900 system.
• How openpyxl Stores Python Values: Types, Dates and None — how dates become numbers.
• Combine Multiple Excel Files into One in Python — the folder loop where mixed systems meet.