Parse Excel Dates into Python datetimes with pandas

A date column that arrives as text is the most common blocker between an Excel export and a working report. You cannot filter on it, group it into months, or write it back with a date format until it is real datetime64 data. pandas has one function for the job — pd.to_datetime — and getting good results from it is a matter of knowing four arguments and one habit: never let a failed parse pass unnoticed. This guide walks the parsing path end to end. It is the practical companion to Working with Dates and Times in Excel Data.

Prerequisites

pip install pandas openpyxl

A sample workbook with a deliberately messy date column, so every example below has something real to chew on:

import pandas as pd

messy = pd.DataFrame({
    "order": [1001, 1002, 1003, 1004, 1005, 1006],
    "invoice_date": ["2026-08-15", "15/08/2026", "Aug 16, 2026",
                     "", "pending", "2026-08-18"],
    "amount": [159.92, 247.50, 137.44, 88.00, 412.10, 96.35],
})
messy.to_excel("orders.xlsx", index=False)

Step 1 — Let read_excel do it when the cells are already dates

If Excel typed the cells as dates, pandas returns datetime64 without being asked. parse_dates is then just insurance that the dtype is what you expect:

import pandas as pd

df = pd.read_excel("clean_orders.xlsx", parse_dates=["invoice_date"])
print(df["invoice_date"].dtype)     # datetime64[ns]

That is the whole story for well-formed files. parse_dates deliberately offers no format, dayfirst or errors argument, so the moment the values are text you move to to_datetime.

Step 2 — Parse text with an explicit format

When you know the layout, say so. An explicit format takes a fast vectorised path and — more importantly — refuses to guess:

import pandas as pd

df = pd.read_excel("orders.xlsx")

df["invoice_date"] = pd.to_datetime(
    df["invoice_date"], format="%d/%m/%Y", errors="coerce"
)

The directives you will use ninety per cent of the time:

Two-digit years are worth a warning. Python's rule maps 69–99 to the 1900s and 00–68 to the 2000s, so %y on a birth-date column will place anyone born before 1969 in the future. If you have two-digit years, resolve the century yourself rather than trusting the cutoff.

Step 3 — Handle ambiguity: dayfirst

03/04/2026 is 4 March to pandas and 3 April to most of Europe. pandas defaults to month-first because that is the US convention, and it does not warn you when a value is ambiguous.

import pandas as pd

s = pd.Series(["03/04/2026", "15/08/2026"])

print(pd.to_datetime(s, dayfirst=False, format="mixed").tolist())
# [Timestamp('2026-03-04'), Timestamp('2026-08-15')]  <- 15 forces day-first here

print(pd.to_datetime(s, dayfirst=True, format="mixed").tolist())
# [Timestamp('2026-04-03'), Timestamp('2026-08-15')]

Note what happened: 15/08/2026 parsed the same way under both settings, because 15 cannot be a month. That is the trap — a column can look correct in spot checks and be wrong on exactly the rows where the day is 12 or less. Roughly a third of dates in a year are ambiguous this way.

The reliable fix is an explicit format rather than dayfirst, because it rejects instead of reinterpreting:

# Anything not in day/month/year form becomes NaT and shows up in the report.
parsed = pd.to_datetime(s, format="%d/%m/%Y", errors="coerce")

Step 4 — Coerce, then report

errors="coerce" is the right default for a batch job, but only paired with an inspection step. On its own it converts a data-quality problem into an invisible one.

import pandas as pd

df = pd.read_excel("orders.xlsx")
raw = df["invoice_date"].astype("string").str.strip()

df["invoice_date"] = pd.to_datetime(raw, format="mixed", errors="coerce")

blank = raw.isna() | (raw == "")
failed = df["invoice_date"].isna() & ~blank

print(f"{int(blank.sum())} blank, {int(failed.sum())} unparseable")
if failed.any():
    print(raw[failed].value_counts())
    # pending    1

For an ingest job, turn that count into a decision rather than a print. A handful of bad rows is data to quarantine; a third of the column failing means the format assumption is wrong:

rate = failed.mean()
if rate > 0.05:
    raise ValueError(
        f"{rate:.1%} of invoice_date failed to parse — "
        "check the expected format before continuing."
    )

quarantine = df.loc[failed]
quarantine.to_excel("rejects.xlsx", index=False)
df = df.loc[~failed].copy()

Writing the rejects to their own workbook so a human can look at them is the same pattern used in highlighting invalid cells in Excel with Python.

Step 5 — Columns that mix serials and text

The genuinely awkward case: some rows were typed as dates in Excel (and arrive as numbers or timestamps), others were typed as text. A single to_datetime call cannot handle both, because the numbers are day counts and would be read as nanoseconds.

Split by type, parse each branch with the right rule, and recombine:

import pandas as pd

col = pd.Series(["2026-08-15", 46249, "15/08/2026", 46250.5, None])

numeric = pd.to_numeric(col, errors="coerce")

# Branch 1: Excel serials — day counts from the 1899-12-30 epoch.
from_serial = pd.to_datetime(
    numeric, unit="D", origin="1899-12-30", errors="coerce"
)

# Branch 2: everything that was not a number, parsed as text.
from_text = pd.to_datetime(
    col.where(numeric.isna()), format="mixed", errors="coerce"
)

parsed = from_serial.fillna(from_text)
print(parsed.tolist())

Guard the serial branch against nonsense. A stray 1 or 999999 is not a date, and silently converting it to 1899 or the year 4637 poisons every downstream aggregate:

PLAUSIBLE = (numeric > 20_000) & (numeric < 60_000)   # ~1954 to ~2064
from_serial = pd.to_datetime(
    numeric.where(PLAUSIBLE), unit="D", origin="1899-12-30", errors="coerce"
)

Common pitfalls and fixes

The failure modes cluster into three families: the epoch is wrong, the field order is wrong, or the dtype never changed at all. Each has a distinct signature in the output, which makes them quick to tell apart once you know what to look for.

The 1970 case is worth dwelling on because the output looks so alien that people assume the data is corrupt. It is not: pd.to_datetime(45292) with no unit treats the integer as nanoseconds since 1970, which is 45 microseconds past midnight on 1 January 1970. Every row lands within a millisecond of the same instant. Seeing a column where every value is 1970-01-01 00:00:00.000045 is a reliable fingerprint for a missing unit="D".

Performance and scale notes

Parsing cost is dominated by whether pandas can take the vectorised path. A quick benchmark on a realistic column makes the gap concrete:

import time
import pandas as pd

col = pd.Series(["15/08/2026"] * 500_000)

for label, kwargs in [
    ("explicit format", {"format": "%d/%m/%Y"}),
    ("mixed inference", {"format": "mixed", "dayfirst": True}),
]:
    start = time.perf_counter()
    pd.to_datetime(col, errors="coerce", **kwargs)
    print(f"{label:<18} {time.perf_counter() - start:6.2f}s")

The explicit format finishes in a fraction of the time, and the gap widens with row count. Three habits follow from that:

Parse once, at the boundary. Convert on ingest, not inside every function that touches the column. Re-parsing an already-datetime64 column is wasted work and can reintroduce errors.

Deduplicate before parsing when cardinality is low. A million rows covering three years hold at most ~1,100 distinct dates:

uniques = col.dropna().unique()
lookup = pd.Series(
    pd.to_datetime(uniques, format="%d/%m/%Y", errors="coerce"), index=uniques
)
parsed = col.map(lookup)

Push the work upstream where you can. If the file comes from a database extract, having the query emit ISO dates removes the whole problem — see exporting SQL query results to Excel with Python. And for very large workbooks, parse chunk by chunk as described in reading large Excel files in chunks so peak memory stays flat.

Conclusion

Parsing Excel dates comes down to four decisions: whether the cells are already typed (use parse_dates), whether you know the layout (pass format), whether the data is day-first (pass dayfirst or, better, an explicit format), and what happens to values that do not parse (errors="coerce", then report). Handle serial numbers as their own branch with the 1899-12-30 origin and a plausibility window. Do the parse once at the ingest boundary, and every downstream grouping, filtering and formatting step gets easier.

Frequently asked questions

Should I use parse_dates on read_excel or to_datetime afterwards? Use parse_dates when the column is already typed as a date in Excel and you just want the dtype. Use to_datetime afterwards whenever the values are text, because it gives you the format, dayfirst and errors arguments that parse_dates does not expose.

Why is 03/04/2026 parsed as 4 March? pandas defaults to month-first parsing. For European data pass dayfirst=True, or better, pass an explicit format like "%d/%m/%Y" so nothing is inferred and anything that does not match is rejected.

What does errors="coerce" actually do to bad values? It replaces each unparseable value with NaT rather than raising. That keeps a batch job running, but you must then check which rows became NaT — otherwise the failures disappear silently.

How do I parse a column that mixes real dates and serial numbers? Split it. Parse the numeric values with unit="D" and origin="1899-12-30", parse the text values with to_datetime, then combine the two results with fillna or combine_first.

Is explicit format really faster? Substantially. With a known format pandas takes a fast vectorised path; without one it falls back to per-value inference. On a column of a million values the difference is typically an order of magnitude.

Related

• Up to the parent: Working with Dates and Times in Excel Data — the serial model and the wider date toolkit.
• Fix Excel Serial Numbers Showing Instead of Dates — the display side of the same problem.
• Group Excel Rows by Month and Quarter with pandas — what you can do once the column parses.
• Check Excel Data Types with pandas — validating dtypes across the whole sheet.
• Fill Missing Values in Excel with pandas fillna — what to do with the rows that came back NaT.