One column, five date formats: how to find and fix it before it breaks your report

Mixed date formats sort wrong, break joins, and quietly destroy month-over-month charts. How to detect the mess in one pass, then parse each format explicitly.

Here is a bug that produces no error message: a date column containing 2026-01-05, 2026/02/11, 03/12/2026 and 2026年4月8日 in the same column. pandas will happily coerce most of it, sort it wrong, and hand you a month-over-month chart that lies.

Why it is worse than it looks

03/12/2026 parses as 3 December under %d/%m/%Y and as 12 March under %m/%d/%Y. Both "work". If your month grouping silently switches convention halfway down the column, two months of revenue land in the wrong bucket and the trend line inverts. Nobody catches it because nothing throws.

Detect it in one pass

import re
from collections import Counter

PATS = [
    (r"^\d{4}-\d{2}-\d{2}$", "YYYY-MM-DD"),
    (r"^\d{4}/\d{2}/\d{2}$", "YYYY/MM/DD"),
    (r"^\d{2}/\d{2}/\d{4}$", "DD/MM or MM/DD"),   # ambiguous on purpose
    (r"^\d{4}年\d{1,2}月\d{1,2}日$", "CN"),
]
hits = Counter()
for v in df["order_date"].dropna().astype(str).head(5000):
    for pat, name in PATS:
        if re.match(pat, v.strip()):
            hits[name] += 1; break
    else:
        hits["unrecognized"] += 1
print(hits)   # the diagnosis, before any parsing

If more than one bucket has counts, the column is mixed. That single print saves an afternoon.

Parse each format explicitly

Never rely on the inferencer. Map format to parser:

def parse_one(v):
    s = str(v).strip()
    m = re.match(r"^(\d{4})年(\d{1,2})月(\d{1,2})日$", s)
    if m: return datetime(*map(int, m.groups()))
    for f in ("%Y-%m-%d", "%Y/%m/%d", "%d/%m/%Y", "%Y-%m-%d %H:%M"):
        try: return datetime.strptime(s, f)
        except ValueError: pass
    return None

df["date"] = df["order_date"].map(parse_one)
print(df["date"].isna().sum())      # must be 0, or you know exactly what is left

Then check the failures, not the successes. A nonzero NaN count is the honest signal.

The DD/MM vs MM/DD trap

When every value has a day ≤ 12, the data cannot tell you which convention it is. Do not guess — check another column (a timezone, a fiscal period, an export locale), or ask. Guessing wrong shifts a third of your rows by up to eleven months.

Fastest way to know if a file has this problem

CSV Doctor counts each format in every date column and prints the breakdown, along with junk-in-numeric-columns, encoding and duplicate checks. It is free — download it here and run python csv_doctor.py --in yourfile.xlsx. If it reports HIGH, fix that before you build anything on top of the data.