Working with Excel Dates and Number Formats

Dates are where spreadsheet automation quietly goes wrong. A workbook that looks correct on screen returns integers to Python, or returns dates that are one day out, or returns a column where half the values are timestamps and half are strings. None of that is a bug in pandas or openpyxl: it is the direct consequence of how Excel stores dates and how it decides what to display.

Excel has no date type. A cell holding 15/07/2026 contains the number 46218 — days since an epoch — plus a number format string that tells the renderer to draw that number as a date. The value and its presentation are separate, stored separately, and lost separately. Every problem on this page follows from that one fact, and generic advice like "parse the dates" fails because it treats display as data.

Prerequisites

python -m venv .venv
source .venv/bin/activate

pip install "pandas>=2.2,<3" "openpyxl>=3.1,<4" xlsxwriter

mkdir -p data out

pandas==2.2.3
openpyxl==3.1.5
XlsxWriter==3.2.0

Step 1: Diagnose What a Cell Actually Holds

Before writing conversion code, find out whether each column contains real serials, real datetimes, or text that merely looks like a date. openpyxl exposes both the value and the format string, which is exactly the pair needed.

# pip install "openpyxl>=3.1,<4"
from datetime import date, datetime
from pathlib import Path
from openpyxl import load_workbook

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

def cell_profile(xlsx_path: Path, sheet: str | None = None, rows: int = 8) -> list[dict]:
    """Report the stored value, its Python type and the cell's number format."""
    if not xlsx_path.exists():
        raise FileNotFoundError(f"No such workbook: {xlsx_path}")
    try:
        book = load_workbook(xlsx_path, data_only=True, read_only=True)
    except Exception as exc:
        raise RuntimeError(f"Could not open {xlsx_path}: {exc}") from exc

    worksheet = book[sheet] if sheet else book.active
    out = []
    for row in worksheet.iter_rows(min_row=2, max_row=1 + rows):
        for cell in row:
            out.append({
                "coordinate": cell.coordinate,
                "value": cell.value,
                "python_type": type(cell.value).__name__,
                "number_format": cell.number_format,
                "is_date": isinstance(cell.value, (datetime, date)),
            })
    book.close()
    return out

if __name__ == "__main__":
    for entry in cell_profile(WORKBOOK)[:12]:
        print(entry)

Three signatures come out of that dump, and they need three different treatments:

{'value': datetime(2026,7,15), 'python_type': 'datetime', 'number_format': 'dd/mm/yyyy'}   # real date
{'value': 46218,               'python_type': 'int',      'number_format': 'General'}       # serial, format lost
{'value': '15/07/2026',        'python_type': 'str',      'number_format': 'General'}       # text

openpyxl converts a cell to datetime only when the number format says it is a date. Strip the format — which is what a CSV round-trip, a paste-as-values, or an export from another system routinely does — and the same number comes back as a bare integer.

Step 2: Convert Serials to Dates

Excel's epoch is 30 December 1899, not 1 January 1900, because the format deliberately reproduces a Lotus 1-2-3 bug that treats 1900 as a leap year. Serial 60 is the non-existent 29 February 1900; every serial above 60 is therefore offset by one relative to a naive count. Using the 1899-12-30 origin absorbs the whole thing.

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

EXCEL_EPOCH = "1899-12-30"          # absorbs the 1900 leap-year bug for serials > 60

def serial_to_datetime(series: pd.Series) -> pd.Series:
    """Convert Excel day serials to datetimes, leaving unconvertible values as NaT."""
    numeric = pd.to_numeric(series, errors="coerce")
    return pd.to_datetime(numeric, unit="D", origin=EXCEL_EPOCH, errors="coerce")

if __name__ == "__main__":
    serials = pd.Series([46218, 45000, 1, 60, None, "not a date"])
    print(pd.DataFrame({"serial": serials, "date": serial_to_datetime(serials)}))

       serial       date
0       46218 2026-07-15
1       45000 2023-03-15
2           1 1899-12-31
3          60 1900-02-28      <- Excel displays 1900-02-29; the date does not exist
4        None        NaT
5  not a date        NaT

Fractional serials carry the time of day: 46218.75 is 18:00 on the same date, because 0.75 of a day is eighteen hours. That is why unit="D" on a float column produces timestamps rather than dates, and why rounding before conversion silently discards times.

Workbooks saved by Excel for Mac before 2011 use the 1904 date system, which shifts everything by 1,462 days. Detect it from the workbook rather than guessing:

# pip install "openpyxl>=3.1,<4"
from openpyxl import load_workbook

book = load_workbook("data/orders.xlsx", read_only=True)
epoch = "1904-01-01" if book.epoch.year == 1904 else "1899-12-30"
print("date system:", epoch)

Step 3: Handle Text That Looks Like a Date

A column exported as text needs parsing, and the trap is ambiguity: 03/04/2026 is 3 April in the UK and 4 March in the US. Never let the parser guess per value.

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

def parse_dates_strict(series: pd.Series, fmt: str = "%d/%m/%Y") -> pd.Series:
    """Parse date text with one explicit format; anything else becomes NaT."""
    parsed = pd.to_datetime(series, format=fmt, errors="coerce")
    failures = series[parsed.isna() & series.notna()]
    if len(failures):
        # Report rather than silently dropping — a second format usually hides here
        print(f"{len(failures)} value(s) did not match {fmt}: {failures.unique()[:5]}")
    return parsed

if __name__ == "__main__":
    raw = pd.Series(["15/07/2026", "03/04/2026", "2026-07-15", "n/a"])
    print(parse_dates_strict(raw))

dayfirst=True without format= is the frequent half-measure: it biases the guess but still guesses, and mixed input produces a column where some rows used one interpretation and some the other. An explicit format with errors="coerce" plus a report of the failures is both stricter and more informative — the same defensive posture applied to numbers in cleaning messy CSV data with pandas.

Step 4: Write Dates and Numbers That Display Correctly

Writing is the mirror image: pass a real datetime and set a number format, or Excel shows a serial.

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

OUT = Path("out/orders_formatted.xlsx")

def write_formatted(frame: pd.DataFrame, dest: Path) -> Path:
    """Write a frame with explicit date and currency formats applied per column."""
    dest.parent.mkdir(parents=True, exist_ok=True)
    with pd.ExcelWriter(dest, engine="openpyxl", datetime_format="dd/mm/yyyy") as writer:
        frame.to_excel(writer, sheet_name="Orders", index=False)
        worksheet = writer.sheets["Orders"]

        formats = {"booked_on": "dd/mm/yyyy", "amount": "£#,##0.00", "share": "0.0%"}
        for index, column in enumerate(frame.columns, start=1):
            code = formats.get(column)
            if not code:
                continue
            for cell in worksheet.iter_cols(min_col=index, max_col=index, min_row=2):
                for one in cell:
                    one.number_format = code       # presentation only; the value is untouched
    return dest

if __name__ == "__main__":
    df = pd.DataFrame({
        "booked_on": pd.to_datetime(["2026-07-15", "2026-07-16"]),
        "amount": [1204.5, 98.0],
        "share": [0.125, 0.4],
    })
    print(write_formatted(df, OUT))

Two things to note. Percentages must be stored as fractions — 0.125 with format 0.0% displays as 12.5%, whereas storing 12.5 displays as 1250.0%. And datetime_format on the writer sets a default for datetime columns, but per-column formats still need applying when different columns want different codes.

Edge Cases and Variants

Timezones and Naive Timestamps

Excel has no timezone concept. Writing a timezone-aware pandas timestamp raises:

ValueError: Excel does not support datetimes with timezones. Please ensure that datetimes are timezone unaware

Decide explicitly whether the sheet is UTC or local, convert, then drop the awareness:

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

aware = pd.to_datetime(["2026-07-15T09:30:00+01:00"], utc=True)
naive_utc = aware.tz_convert("UTC").tz_localize(None)      # store UTC, document it
naive_local = aware.tz_convert("Europe/London").tz_localize(None)

Record the choice in the sheet — a header cell or a column name suffixed _utc — because nothing in the file will preserve it otherwise.

Dates Before 1900

Excel cannot represent them at all; serials start at 1899-12-31. A pandas column containing 1885 birthdates will write as a negative serial and render as #######. Store historical dates as ISO text with the @ format, which keeps them intact and sortable.

Two Formats in One Column

Exports that concatenate sources often mix 15/07/2026 and 2026-07-15. Parse in passes rather than with a permissive guesser:

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

def parse_multi_format(series: pd.Series, formats: list[str]) -> pd.Series:
    """Try each format in turn, filling only the values still unparsed."""
    result = pd.Series(pd.NaT, index=series.index, dtype="datetime64[ns]")
    for fmt in formats:
        pending = result.isna() & series.notna()
        if not pending.any():
            break
        result.loc[pending] = pd.to_datetime(series[pending], format=fmt, errors="coerce")
    return result

if __name__ == "__main__":
    mixed = pd.Series(["15/07/2026", "2026-07-16", "17-Jul-2026"])
    print(parse_multi_format(mixed, ["%d/%m/%Y", "%Y-%m-%d", "%d-%b-%Y"]))

Validation

Assert on both the type and the plausibility of the range. A serial misread as a date lands centuries away, which a range check catches instantly.

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

def assert_dates_sane(frame: pd.DataFrame, column: str,
                      earliest: str = "2000-01-01", latest: str = "2100-01-01") -> None:
    """Fail on a non-datetime column, on nulls introduced by parsing, or on wild dates."""
    series = frame[column]
    assert pd.api.types.is_datetime64_any_dtype(series), (
        f"{column}: dtype is {series.dtype}, not datetime — parsing did not happen"
    )
    null_pct = series.isna().mean() * 100
    assert null_pct < 5, f"{column}: {null_pct:.1f}% NaT — the format is wrong for most rows"

    low, high = pd.Timestamp(earliest), pd.Timestamp(latest)
    outside = series[(series < low) | (series > high)].dropna()
    assert outside.empty, (
        f"{column}: {len(outside)} date(s) outside {earliest}..{latest}, e.g. {outside.iloc[0]}"
    )
    print(f"{column}: {len(series):,} value(s) parsed and within range")

Round-trip verification is the other half. Write the workbook, read it back with data_only=True, and compare against the source frame — that catches format codes that render correctly on your machine and not under a different locale.

Performance and Scale Notes

Parsing dates with an explicit format= uses a fast path; without it, pandas falls back to a per-value dateutil parse that is roughly an order of magnitude slower. On a million-row column that is the difference between under a second and half a minute, so the explicit format is a performance fix as well as a correctness one.

infer_datetime_format is redundant on pandas 2.x — the fast path is automatic when the format is consistent. When it is not consistent, the multi-pass function above beats inference because it fails predictably rather than per row.

For very large workbooks, applying a number format cell-by-cell through openpyxl is slow: each assignment touches the style table. Writing with xlsxwriter and a column-level format is dramatically faster, at the cost of not being able to modify an existing file:

# pip install xlsxwriter "pandas>=2.2,<3"
with pd.ExcelWriter("out/big.xlsx", engine="xlsxwriter") as writer:
    frame.to_excel(writer, sheet_name="Data", index=False)
    book, sheet = writer.book, writer.sheets["Data"]
    date_fmt = book.add_format({"num_format": "dd/mm/yyyy"})
    sheet.set_column("A:A", 12, date_fmt)      # one call for the whole column

Troubleshooting

Complete Working Script

#!/usr/bin/env python3
"""Normalise dates and numbers in a workbook and write a formatted copy.

    pip install "pandas>=2.2,<3" "openpyxl>=3.1,<4"
    python normalise_dates.py data/orders.xlsx out/orders_clean.xlsx --date-col booked_on
"""
from __future__ import annotations

import argparse
import sys
from pathlib import Path

import pandas as pd
from openpyxl import load_workbook

def excel_origin(xlsx_path: Path) -> str:
    """1904 or 1900 date system, read from the workbook itself."""
    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()

def normalise(series: pd.Series, origin: str, formats: list[str]) -> pd.Series:
    """Turn serials, datetimes and date text into one datetime column."""
    if pd.api.types.is_datetime64_any_dtype(series):
        return series
    numeric = pd.to_numeric(series, errors="coerce")
    result = pd.to_datetime(numeric, unit="D", origin=origin, errors="coerce")
    for fmt in formats:                                   # text rows the serial pass missed
        pending = result.isna() & series.notna()
        if not pending.any():
            break
        result.loc[pending] = pd.to_datetime(series[pending], format=fmt, errors="coerce")
    return result

def main(argv: list[str] | None = None) -> int:
    parser = argparse.ArgumentParser(description="Normalise Excel dates and number formats.")
    parser.add_argument("source", type=Path)
    parser.add_argument("output", type=Path)
    parser.add_argument("--date-col", action="append", required=True)
    parser.add_argument("--sheet", default=0)
    args = parser.parse_args(argv)

    if not args.source.exists():
        print(f"error: no such workbook: {args.source}", file=sys.stderr)
        return 1

    frame = pd.read_excel(args.source, sheet_name=args.sheet)
    origin = excel_origin(args.source)
    for column in args.date_col:
        if column not in frame.columns:
            print(f"error: no column {column!r}", file=sys.stderr)
            return 1
        frame[column] = normalise(frame[column], origin, ["%d/%m/%Y", "%Y-%m-%d", "%d-%b-%Y"])
        unparsed = frame[column].isna().sum()
        if unparsed:
            print(f"warning: {unparsed} unparsed value(s) in {column}", file=sys.stderr)

    args.output.parent.mkdir(parents=True, exist_ok=True)
    with pd.ExcelWriter(args.output, engine="openpyxl", datetime_format="dd/mm/yyyy") as writer:
        frame.to_excel(writer, index=False, sheet_name="Data")
    print(f"wrote {args.output} ({len(frame):,} rows, epoch {origin})")
    return 0

if __name__ == "__main__":
    raise SystemExit(main())

FAQ

Why does Excel think 1900 was a leap year? Lotus 1-2-3 had the bug, and Excel reproduced it deliberately so that spreadsheets could be exchanged between the two. Using 1899-12-30 as the origin makes the arithmetic come out right for every serial above 60, which covers all real-world data.

Should I store dates as text to avoid all this? Only for dates outside Excel's range, or where the value is really a label. Text dates cannot be sorted chronologically, filtered by range, or used in date arithmetic in the sheet, which usually costs the recipient more than it saves you.

How do I keep leading zeros on codes like 00417? Store the value as a string and set the cell format to @. Any numeric dtype discards the zeros before the format is ever applied — see fix pandas read_excel unnamed columns for the related header-level surprises.

Why does my date column come back as object from read_excel? Because the column mixes real datetimes with text. pandas cannot promote to datetime64 while strings are present, so it falls back to object — normalise with the multi-pass parser, then assert the dtype.

Do number formats survive a CSV export? No. CSV has no formats, only text, so £1,204.50 becomes a string that will not parse as a number on the way back in. Use thousands="," on the read, or export to Parquet or xlsx when the formatting matters.

Agreeing a House Convention

Most date pain in a spreadsheet pipeline comes from having no stated convention, so every script invents one. Four decisions, written down once, remove nearly all of it.

Pick a storage form and use it everywhere internally: ISO dates as text between systems, real datetimes inside pandas, serials only when Excel itself is the destination. Mixing them per stage is what produces columns where half the rows parsed and half did not.

Decide the timezone rule and record it in the column name. A column called booked_on_utc is unambiguous; one called booked_on will eventually be read as local time by somebody, and the error is invisible until a month boundary.

Agree the display format with whoever reads the output, and apply it as a number format rather than by writing formatted text. The value stays sortable and filterable, and the format can change without touching the data.

Finally, decide what an unknown date is. NaT and an empty cell are both defensible; a sentinel like 1900-01-01 is not, because it will be aggregated into a report at some point and will look like real history.

Two smaller conventions round it out. Decide whether a date-only value is stored as a date or as a timestamp at midnight, because mixing the two makes equality comparisons fail in ways that look like data problems. And decide who owns the financial calendar: if a month means something other than a calendar month to the business, that mapping belongs in one documented place rather than being re-derived in each report that needs it.

Write both conventions into the repository's README rather than into a comment in one script, so the next report that needs a date does not have to reverse-engineer the rule from an existing query.

Related

• Fix Excel Dates Showing as Numbers in pandas — the serial-conversion fix on its own
• Fix openpyxl Number Format Not Applied — formats that do not appear in the saved file
• Reading Excel Files with Python — engines, sheets and header handling
• Writing Excel Formulas and Charts with openpyxl — styling beyond number formats
• Cleaning Messy CSV Data with pandas — the same coercion discipline applied to text files

Part of Python for Excel & CSV Data Processing.