Fix Mixed Day-First and Month-First Dates in Excel Data

03/04/2026 is 3 April in London and March 4 in New York. When a sheet combines rows typed by people in both places — or exports from systems configured differently — the date column becomes a mixture, and no single parsing setting is right for every row. Worse, the convenient options look right: pandas parses the whole column without an error and the dates come out plausible. They are just wrong for some rows, and nothing tells you which. This page shows how to find out what the column actually contains, decide each row's convention from evidence, parse with explicit formats, and report the rows that genuinely cannot be decided. It belongs to Working with Dates and Times in Excel Data.

Prerequisites

pip install pandas openpyxl

orders_mixed_dates.xlsx has eleven orders with a Source column. London writes day/month/year, New York month/day/year, the ERP exports ISO dates, one manual row holds a real Excel date (a number with a date format), one has a written-out month, and the Lyon office has a single row that is ambiguous on its own.

Step 1 — See why one setting cannot work

import pandas as pd

orders = pd.read_excel("orders_mixed_dates.xlsx")
print(orders["OrderDate"].map(type).value_counts().to_dict())
text = orders["OrderDate"].astype(str)
attempts = pd.DataFrame({
    "raw": text,
    "mixed, dayfirst": pd.to_datetime(text, format="mixed", dayfirst=True).dt.date,
    "mixed, monthfirst": pd.to_datetime(text, format="mixed", dayfirst=False).dt.date,
})
print(attempts.assign(Source=orders["Source"]).to_string(index=False))

The column holds strings and one datetime — openpyxl converted the real Excel date. Parsing with format="mixed", dayfirst=True gets London right and New York's ambiguous rows wrong, as expected. But look at two rows where it should not matter. 04/13/2026 cannot be day-first — there is no month 13 — yet the parser quietly swaps it to 13 April instead of complaining. And the ERP's 2026-04-05, an unambiguous ISO date, comes back as 4 May: with format="mixed", the dayfirst flag is applied to ISO strings too. The real Excel date fares no better once it has been turned into text for parsing: 2026-04-09 00:00:00 comes back as 4 September. Switching to month-first fixes those rows and breaks London. No single setting works, and neither raises an error.

Step 2 — Classify every value

Parse nothing yet. Describe each value instead: is it already a date, an ISO string, a slash date whose first or second part is above 12, or a slash date where both parts could be a month?

import datetime as dt
import re

SLASH = re.compile(r"^(\d{1,2})[/.-](\d{1,2})[/.-](\d{4})$")
ISO = re.compile(r"^\d{4}-\d{2}-\d{2}$")

def classify(value):
    if isinstance(value, (dt.datetime, pd.Timestamp)):
        return "excel date"
    s = str(value).strip()
    if ISO.match(s):
        return "iso"
    m = SLASH.match(s)
    if m:
        a, b = int(m.group(1)), int(m.group(2))
        if a > 12 and b <= 12:
            return "day-first"
        if b > 12 and a <= 12:
            return "month-first"
        if a <= 12 and b <= 12:
            return "ambiguous"
        return "invalid"
    return "other text"

orders["Kind"] = orders["OrderDate"].map(classify)
print(orders[["OrderID", "Source", "OrderDate", "Kind"]].to_string(index=False))

Only three rows are decided by their own digits — 21/04/2026 (day-first), 04/13/2026 and 12/30/2026 (month-first). Four slash dates are ambiguous, the ERP rows are ISO, one row is a genuine Excel date, and one is written-out text. That is typical: in most months, most slash dates are ambiguous, because both the day and the month are 12 or less on 40% of days.

Step 3 — Infer each source's convention from its unambiguous rows

A source rarely mixes conventions internally. If any of its rows are unambiguously day-first and none month-first, the ambiguous ones are day-first too:

votes = (orders[orders["Kind"].isin(["day-first", "month-first"])]
         .groupby("Source")["Kind"].agg(lambda k: set(k)))
convention = {src: kinds.pop() for src, kinds in votes.items() if len(kinds) == 1}
conflicts = [src for src, kinds in votes.items() if len(kinds) > 1]
print("convention by source:", convention)
print("sources with conflicting evidence:", conflicts)

London has one unambiguous day-first row, so its other rows are read day-first; New York has two month-first rows. Lyon has no deciding row at all, so its convention is unknown. A source that shows both kinds of evidence goes into conflicts — a sign that the sheet itself is mixed and needs a human. When sources are not recorded in the data, file names, sheet names or the user who submitted the file can serve the same purpose.

Step 4 — Parse every row with an explicit format

With a kind and a convention per row, each value is parsed with a format that cannot be misread:

FORMATS = {"day-first": "%d/%m/%Y", "month-first": "%m/%d/%Y", "iso": "%Y-%m-%d"}

def parse(row):
    kind, value = row["Kind"], row["OrderDate"]
    if kind == "excel date":
        return pd.Timestamp(value)
    if kind == "ambiguous":
        kind = convention.get(row["Source"], "unknown")
    if kind in FORMATS:
        s = re.sub(r"[.-]", "/", str(value).strip()) if kind != "iso" else str(value).strip()
        return pd.to_datetime(s, format=FORMATS[kind])
    if kind == "other text":
        return pd.to_datetime(str(value), format="%d %b %Y", errors="coerce")
    return pd.NaT

orders["Date"] = orders.apply(parse, axis=1)
orders["Needs review"] = orders["Date"].isna()
print(orders[["OrderID", "Source", "OrderDate", "Date", "Needs review"]].to_string(index=False))

Every parse uses a fixed format, so %d/%m/%Y will raise rather than swap if a value does not fit — the opposite of format="mixed"'s helpful guessing. The ISO rows come out as 5 April and 2 November, New York's 05/02/2026 as 2 May, London's 03/04/2026 as 3 April, and Lyon's 06/07/2026 as NaT, flagged for review. For a single, consistent column, the simpler approach in Parse Excel Dates into Python Datetimes with pandas is enough.

Step 5 — Report what was decided and how

The people who own the data should see each decision, not just the result. Write the review rows and the evidence to the output:

orders["Rule"] = orders["Kind"].where(orders["Kind"] != "ambiguous",
                                      "ambiguous → " + orders["Source"].map(convention).fillna("unknown"))
with pd.ExcelWriter("orders_dates_checked.xlsx", engine="xlsxwriter", datetime_format="dd mmm yyyy") as xw:
    orders[["OrderID", "Source", "OrderDate", "Date", "Rule", "Needs review"]].astype({"OrderDate": str}).to_excel(
        xw, sheet_name="Dates", index=False)
    orders[orders["Needs review"]].to_excel(xw, sheet_name="Review", index=False)
print(orders["Rule"].value_counts().to_dict())

The output shows each original text, the parsed date written as dd mmm yyyy — a format nobody can misread — and the rule that produced it. The review sheet lists the Lyon order, which someone has to confirm with the office. Writing dates with month names in reports is cheap insurance against the next round of confusion.

Step 6 — Sanity-check the parsed dates

Even with explicit formats, a wrong convention for a whole source produces valid-looking dates. A plausibility check against what you know about the data catches it: order dates should fall inside the reporting period and not in the future.

period_start, period_end = pd.Timestamp("2026-01-01"), pd.Timestamp("2026-12-31")
outside = orders[(orders["Date"] < period_start) | (orders["Date"] > period_end)]
print("outside the period:", outside["OrderID"].tolist())
by_month = orders.dropna(subset=["Date"]).groupby(orders["Date"].dt.to_period("M"))["OrderID"].count()
print(by_month.to_dict())

No order falls outside 2026 here. The monthly counts are the second check: if a source's convention had been inferred wrongly, its orders would pile up in the first twelve days of the wrong months, or vanish from months with days above 12, and the distribution would look lumpy next to previous periods. Comparing this month's distribution with last month's is a cheap guard to add to a recurring job.

Common pitfalls

• format="mixed" with dayfirst. It swaps unambiguous month-first dates and reinterprets ISO dates; never use it on a mixed column.
• Parsing before classifying. Once parsed, you cannot tell a correct date from a swapped one.
• Assuming most dates are unambiguous. Days 1–12 make up about 40% of the calendar.
• Inferring from one row per source. One unambiguous row is evidence, not proof; flag sources with conflicting evidence.
• Excel dates as strings. Real date cells arrive as datetime objects; convert them directly, never via text.

Conclusion

A column that mixes day-first, month-first, ISO and real Excel dates cannot be parsed with one setting, and format="mixed" with dayfirst silently swaps the rows it gets wrong — ISO dates included. Classify each value, infer each source's convention from its unambiguous rows, parse every row with an explicit format, and send the rows that remain ambiguous to a person, with the rule used for every date written alongside the result.

Frequently asked questions

How do I parse a column with both dd/mm/yyyy and mm/dd/yyyy dates? Classify each value first: if the first part is above 12 it is day-first, if the second part is above 12 it is month-first, otherwise it is ambiguous. Resolve ambiguous rows from the convention of their source, then parse each row with an explicit format such as %d/%m/%Y or %m/%d/%Y.

Why does pd.to_datetime with dayfirst=True change my ISO dates? With format="mixed", pandas applies the dayfirst preference to every value, including ISO strings such as 2026-04-05, which it then reads as 4 May. Parse ISO dates separately with format="%Y-%m-%d".

Why doesn't pandas raise an error for 04/13/2026 with dayfirst=True? dayfirst is a preference, not a rule: when the value cannot be day-first, the parser swaps to month-first silently. Use an explicit format, which raises or returns NaT for values that do not fit.

How can I tell whether 06/07/2026 is June or July? From the value alone you cannot. Look for unambiguous dates from the same source, or ask the sender; otherwise flag the row for review rather than guessing.

Related

• Up one level: Working with Dates and Times in Excel Data — dates in spreadsheet data.
• Convert Text Dates Like 20240131 with pandas — compact date strings.
• Fix Excel Serial Numbers Showing Instead of Dates — when dates arrive as numbers.
• Localise an Excel Report for Other Languages and Locales — writing dates for different audiences.