Python Doc & Data Automation

Fix Excel Dates Showing as Numbers in pandas

The sheet shows dates. pandas returns integers:

>>> frame = pd.read_excel("data/orders.xlsx")
>>> frame["booked_on"].head(3)
0    46218
1    46219
2    46220
Name: booked_on, dtype: int64

Converting with a naive epoch produces something that looks plausible and is one day wrong:

>>> pd.to_datetime(frame["booked_on"], unit="D", origin="1900-01-01")
0   2026-07-16      # the sheet says 15 July

Both symptoms come from the same source: the cell stores a day serial, and the mapping from serial to calendar date is not the obvious one.

Root Cause

Excel stores a date as the number of days since an epoch, and pairs it with a number format that decides how it is drawn. When the format is present, pandas and openpyxl reconstruct a datetime. When the format has been stripped — by a CSV round trip, a paste-as-values, or an export from a reporting tool — the same number arrives as a bare integer with nothing to say it is a date.

The one-day error has a separate cause. Excel treats 1900 as a leap year, reproducing a Lotus 1-2-3 bug for compatibility, so its calendar contains a 29 February 1900 that never existed. Serial 1 is 1 January 1900, but every serial above 60 is shifted one day later than a strict count would give. Using 1899-12-30 as the origin cancels the phantom day exactly.

The phantom day that offsets every Excel serial Two parallel number lines. The upper line is the real calendar running from 27 February 1900 to 2 March 1900. The lower line is Excel's serial sequence, which inserts a 29 February 1900 at serial 60. From serial 61 onwards Excel is one day ahead of a strict count, which is why using 1899-12-30 as the conversion origin produces the correct date. Why 1899-12-30 is the right origin Real calendar 28 Feb 1 Mar 2 Mar Excel serials 60 61 = 29 Feb 62 63 29 Feb 1900 never existed every later serial is one day ahead

Minimal Diagnostic

Check whether the column holds serials, datetimes or text before converting anything, and read the workbook's date system rather than assuming it.

# pip install "pandas>=2.2,<3" "openpyxl>=3.1,<4"
from pathlib import Path
import pandas as pd
from openpyxl import load_workbook

XLSX = Path("data/orders.xlsx")

def date_column_profile(xlsx_path: Path, column: str, sheet=0) -> dict:
    """Report the dtype, a sample value and the workbook's epoch."""
    if not xlsx_path.exists():
        raise FileNotFoundError(f"No such workbook: {xlsx_path}")
    try:
        frame = pd.read_excel(xlsx_path, sheet_name=sheet)
    except Exception as exc:
        raise RuntimeError(f"Could not read {xlsx_path}: {exc}") from exc
    if column not in frame.columns:
        raise KeyError(f"No column {column!r}; found {list(frame.columns)[:8]}")

    book = load_workbook(xlsx_path, read_only=True)
    epoch_year = book.epoch.year
    book.close()

    series = frame[column]
    return {
        "dtype": str(series.dtype),
        "sample": series.dropna().head(3).tolist(),
        "looks_like_serial": pd.api.types.is_numeric_dtype(series)
                             and series.dropna().between(20000, 60000).all(),
        "workbook_epoch": epoch_year,
        "origin_to_use": "1904-01-01" if epoch_year == 1904 else "1899-12-30",
    }

if __name__ == "__main__":
    print(date_column_profile(XLSX, "booked_on"))

{'dtype': 'int64', 'sample': [46218, 46219, 46220], 'looks_like_serial': True,
 'workbook_epoch': 1899, 'origin_to_use': '1899-12-30'}

The between(20000, 60000) heuristic is a sanity band, not a proof: serial 20000 is 1954 and 60000 is 2064, which brackets essentially all business data. A numeric column outside that range is more likely a quantity than a date.

The Fix

# pip install "pandas>=2.2,<3"
import pandas as pd

EXCEL_EPOCH = "1899-12-30"      # cancels the phantom 29 Feb 1900

def serial_to_datetime(series: pd.Series, origin: str = EXCEL_EPOCH) -> pd.Series:
    """Convert Excel day serials to datetimes; unconvertible values become NaT."""
    numeric = pd.to_numeric(series, errors="coerce")   # tolerate stray text rows
    return pd.to_datetime(numeric, unit="D", origin=origin, errors="coerce")

if __name__ == "__main__":
    frame = pd.read_excel("data/orders.xlsx")
    frame["booked_on"] = serial_to_datetime(frame["booked_on"])
    print(frame[["booked_on"]].head())
    print(frame["booked_on"].dtype)     # datetime64[ns]

Three details in those four lines. pd.to_numeric(..., errors="coerce") runs first so a stray "N/A" becomes NaN instead of raising. unit="D" treats the number as days, which means a fractional serial keeps its time of day rather than being truncated. And errors="coerce" on the datetime conversion turns an out-of-range serial into NaT rather than aborting the whole column.

Variant Fix 1: The Format Is Present but pandas Still Returns Numbers

If openpyxl reports a date format yet pandas gives integers, the column is mixed: some cells are real dates and some are numbers pasted as values. read_excel cannot promote to datetime64 while integers are present, so it falls back.

# pip install "pandas>=2.2,<3"
import pandas as pd

def normalise_mixed(series: pd.Series, origin: str = "1899-12-30") -> pd.Series:
    """Combine already-parsed datetimes with rows that are still serials."""
    result = pd.to_datetime(series, errors="coerce")             # real datetimes convert here
    still_missing = result.isna() & series.notna()
    numeric = pd.to_numeric(series[still_missing], errors="coerce")
    result.loc[still_missing] = pd.to_datetime(
        numeric, unit="D", origin=origin, errors="coerce"
    )
    return result

if __name__ == "__main__":
    mixed = pd.Series([pd.Timestamp("2026-07-15"), 46219, "2026-07-17", None])
    print(normalise_mixed(mixed))

Run the datetime pass first. Doing the serial pass first would convert a genuine year like 2026 — stored as a number in some exports — into 1905, which is exactly the kind of silent corruption that survives review.

Variant Fix 2: The 1904 Date System

Workbooks created by older Excel for Mac count from 1 January 1904. Every date read with the 1900 origin lands 1,462 days early, which is four years and one day — an error big enough to notice and small enough to be dismissed as a data problem.

# pip install "openpyxl>=3.1,<4" "pandas>=2.2,<3"
from openpyxl import load_workbook
import pandas as pd

def origin_for(xlsx_path: str) -> str:
    """Read the date system from the workbook rather than assuming 1900."""
    book = load_workbook(xlsx_path, read_only=True)
    try:
        return "1904-01-01" if book.epoch.year == 1904 else "1899-12-30"
    finally:
        book.close()

if __name__ == "__main__":
    path = "data/mac_export.xlsx"
    frame = pd.read_excel(path)
    frame["booked_on"] = pd.to_datetime(
        pd.to_numeric(frame["booked_on"], errors="coerce"),
        unit="D", origin=origin_for(path), errors="coerce",
    )

Detecting rather than assuming costs one function call and removes an entire class of "the dates are four years out" incident from batch jobs that ingest workbooks from many sources.

Variant Fix 3: Times, Durations and Fractions

A serial's fractional part is the fraction of a day. 0.5 is noon; a cell formatted as [h]:mm holding 1.25 is 30 hours, not 1.25 hours.

# pip install "pandas>=2.2,<3"
import pandas as pd

serials = pd.Series([46218.0, 46218.5, 46218.75])
timestamps = pd.to_datetime(serials, unit="D", origin="1899-12-30")
print(timestamps)
# 2026-07-15 00:00:00 / 12:00:00 / 18:00:00

durations = pd.to_timedelta(pd.Series([0.5, 1.25]), unit="D")
print(durations)     # 0 days 12:00:00 / 1 days 06:00:00

Rounding a serial before conversion throws the time away silently. If the source has times, convert first and truncate afterwards with .dt.normalize() when you genuinely want dates only.

Safe conversion order Four ordered steps. First read the workbook epoch to choose between the 1899-12-30 and 1904-01-01 origins. Second convert any values that are already datetimes or date text. Third convert the remaining numeric values as day serials. Fourth assert that the resulting dates fall in a plausible range. A warning notes that reversing steps two and three converts stray year numbers into 1905 dates. 1. Read epoch book.epoch.year 1899 or 1904 2. Datetimes to_datetime first real dates and text 3. Serials only what is left unit="D" + origin 4. Assert range check and null share Swapping steps 2 and 3 turns a stray year like 2026 into 1905-07-18 the values still look like dates, so review will not catch it

Verification

A wrong origin does not produce errors — it produces plausible dates. Assert on the range, on the null share, and where possible on a value you can check by eye against the sheet.

# pip install "pandas>=2.2,<3"
import pandas as pd

def assert_dates_correct(frame: pd.DataFrame, column: str,
                         known: dict[int, str] | None = None) -> None:
    """Fail on non-datetime dtype, heavy NaT, implausible range or a wrong known value."""
    series = frame[column]
    assert pd.api.types.is_datetime64_any_dtype(series), f"{column}: dtype is {series.dtype}"

    null_pct = series.isna().mean() * 100
    assert null_pct < 5, f"{column}: {null_pct:.1f}% NaT — wrong origin or mixed formats"

    low, high = pd.Timestamp("2000-01-01"), pd.Timestamp("2100-01-01")
    outside = series[(series < low) | (series > high)].dropna()
    assert outside.empty, f"{column}: {len(outside)} implausible date(s), e.g. {outside.iloc[0].date()}"

    for row_index, expected in (known or {}).items():
        actual = series.iloc[row_index]
        assert str(actual.date()) == expected, (
            f"row {row_index}: expected {expected}, got {actual.date()} — origin is off"
        )
    print(f"{column}: {series.notna().sum():,} date(s) verified")

if __name__ == "__main__":
    df = pd.read_excel("data/orders.xlsx")
    df["booked_on"] = pd.to_datetime(pd.to_numeric(df["booked_on"], errors="coerce"),
                                     unit="D", origin="1899-12-30", errors="coerce")
    assert_dates_correct(df, "booked_on", known={0: "2026-07-15"})

The known argument is the one that catches an off-by-one or an off-by-four-years: open the sheet once, read the first date, and pin it in the test. Everything else in this class of bug hides behind values that look reasonable. For the broader treatment of formats, timezones and writing dates back out, see working with Excel dates and number formats.

Sanity band for an Excel date serial A number line of serial values. Below twenty thousand the date is before 1954 and the value is more likely a quantity than a date. Between twenty thousand and sixty thousand covers 1954 to 2064, which brackets essentially all business data. Above sixty thousand the date is beyond 2064 and again suggests a quantity. The band is a heuristic for deciding whether a numeric column is a date at all. Is this number a date serial? < 20,000 before 1954 20,000 to 60,000 1954 to 2064 — plausible > 60,000 after 2064 46,218 falls inside the band and converts to 15 July 2026 A column of values like 1,204.50 does not — it is money, and converting it would be nonsense

FAQ

Why 1899-12-30 rather than 1900-01-01? Because Excel's calendar contains a 29 February 1900 that does not exist. Moving the origin back two days from 1 January 1900 compensates for both the phantom day and the fact that Excel counts from serial 1 rather than 0.

Is xlrd responsible for this? No. xlrd no longer reads .xlsx at all — that error is covered in fix xlrd error reading xlsx files. Serial values come from the workbook, whichever engine reads it.

Why do some rows convert and others do not? The column is mixed. Convert real datetimes first, then treat only the remaining numeric values as serials, as normalise_mixed above does.

Can I stop this happening at the source? Yes — ask for the export in ISO text (2026-07-15) or as CSV with a documented date format. A date that survives as text with a known format is more portable than one that depends on a number format being preserved.

Do I need to worry about the 1904 system in practice? Rarely, but the check is one line and the failure is a silent four-year shift. Read book.epoch in any job that accepts workbooks from outside your own team.

When the Column Is Not a Date at All

A numeric column in the plausible serial range is not proof of anything. Quantities, reference numbers and prices all land between twenty and sixty thousand, and converting one of them produces a column of confident, meaningless dates that nothing downstream will question.

Use the column's name and its neighbours as the tie-break, and where both are ambiguous, convert one value and check it against the source workbook by eye. Thirty seconds of confirmation is considerably cheaper than a report built on quantities that were silently reinterpreted as days.

Part of Working with Excel Dates and Number Formats.

/html>