A column containing 2026-01-05, 2026/02/11, 03/12/2026, and 2026年4月8日 needs an explicit parsing policy. The result depends on the parser, version, and options: pandas may reject unmatched formats, turn them into NaT when asked to coerce errors, or infer a date you did not intend. It does not inevitably coerce mixed dates and sort them incorrectly.

Why a successful parse is not enough

03/12/2026 is 3 December under %d/%m/%Y and 12 March under %m/%d/%Y. Both are valid dates. A zero missing-value count cannot establish which interpretation was intended.

A wrong interpretation can misallocate revenue, change date-key joins, or make rolling calculations use the wrong business sequence. Sorting parsed timestamps orders those interpreted dates correctly; it cannot recover the intended dates. Even a completely misinterpreted column can be monotonic after sorting.

Excel data can contain both real date cells and text that looks like a date. openpyxl can return Python date/time objects for recognized date cells, while text remains text. Numeric serial dates require the workbook's date system (1900 or 1904) and suitable cell/context information; an arbitrary integer is not evidence of an Excel date. See openpyxl's date documentation.

Detect shapes before parsing

Keep the original values. This full-column scan distinguishes typed dates, numeric values, and string shapes. It checks structure, not calendar validity.

import re from collections import Counter from datetime import date, datetime from numbers import Number import pandas as pd PATS = [ (r"\d{4}-\d{2}-\d{2}", "YYYY-MM-DD"), (r"\d{4}/\d{1,2}/\d{1,2}", "YYYY/M/D"), (r"\d{1,2}/\d{1,2}/\d{4}", "D/M or M/D"), (r"\d{4}年\d{1,2}月\d{1,2}日", "CN"), (r"\d{4}-\d{2}-\d{2} \d{2}:\d{2}(?::\d{2})?", "timestamp"), ] def shape(v): if pd.isna(v): return "missing" if isinstance(v, (datetime, date)): return "typed date/time" if isinstance(v, Number): return "numeric: needs source context" if not isinstance(v, str): return "unsupported type" s = v.strip() if not s: return "blank" for pattern, name in PATS: if re.fullmatch(pattern, s): return name return "unrecognized" print(Counter(df["order_date"].map(shape)))

The slash-date bucket is only potentially ambiguous: 13/02/2026 has one valid day/month interpretation, whereas 03/12/2026 has two. A prefix sample such as head(5000) can miss a format that appears later; it is not a completeness check. If a full scan is expensive, scan in chunks rather than treating a clean prefix as evidence about every row.

Parse explicitly and quarantine unresolved values

The parser below is for scalar date/time cells. It accepts listed naive date/time formats and preserves already-typed values. It leaves ambiguous slash dates unresolved by default. Set slash_order only from reliable source metadata, not from whichever format happens to match first. For multiple sources with different conventions, apply that policy separately by source.

# Imports and PATS from the detection example are required. def parse_one(v, *, slash_order=None): if slash_order not in (None, "DMY", "MDY"): raise ValueError("slash_order must be None, DMY, or MDY") if pd.isna(v): return None, "missing" if isinstance(v, datetime): if v.tzinfo is not None: return None, "timezone policy required" return v, "typed date/time" if isinstance(v, date): return datetime(v.year, v.month, v.day), "typed date" if not isinstance(v, str): return None, "unsupported type or numeric serial" s = v.strip() if not s: return None, "blank" try: if re.fullmatch(PATS[0][0], s): return datetime.strptime(s, "%Y-%m-%d"), "ISO date" if re.fullmatch(PATS[1][0], s): return datetime.strptime(s, "%Y/%m/%d"), "year-first slash" if re.fullmatch(PATS[2][0], s): a, b, year = map(int, s.split("/")) if slash_order: fmt = "%d/%m/%Y" if slash_order == "DMY" else "%m/%d/%Y" return datetime.strptime(s, fmt), f"source policy: {slash_order}" candidates = set() for month, day in ((b, a), (a, b)): try: candidates.add(datetime(year, month, day)) except ValueError: pass if len(candidates) == 1: return candidates.pop(), "single valid slash interpretation" return None, "ambiguous slash date" if candidates else "invalid date" if re.fullmatch(PATS[3][0], s): year, month, day = map(int, re.findall(r"\d+", s)) return datetime(year, month, day), "CN date" if re.fullmatch(PATS[4][0], s): fmt = "%Y-%m-%d %H:%M:%S" if s.count(":") == 2 else "%Y-%m-%d %H:%M" return datetime.strptime(s, fmt), "timestamp" except ValueError: return None, "invalid date" return None, "unrecognized" df["order_date_raw"] = df["order_date"].copy() results = df["order_date_raw"].map(parse_one) df["date"] = pd.to_datetime(results.map(lambda r: r[0]), errors="coerce") df["date_parse_status"] = results.map(lambda r: r[1]) # Conversion may reject a value outside the installed pandas datetime range. outside_range = results.map(lambda r: r[0] is not None) & df["date"].isna() df.loc[outside_range, "date_parse_status"] = "outside pandas datetime range" unresolved = df["date"].isna() print(df["date_parse_status"].value_counts()) print(df.loc[unresolved, ["order_date_raw", "date_parse_status"]].head(20)) if unresolved.any(): raise ValueError("Review missing, invalid, or ambiguous dates before reporting")

Invalid Chinese-format dates are caught rather than escaping as exceptions halfway through the mapping. Typed datetimes with seconds or microseconds are preserved instead of being converted to text and rejected by a shorter format string. Missing dates are tracked separately; the final gate treats dates as required. If they are optional, define that exception explicitly without treating invalid or ambiguous entries as acceptable missing values.

For time-zone-aware inputs, establish the intended zone and conversion rules separately. Do not silently strip a timezone to satisfy a naive-date pipeline.

The DD/MM versus MM/DD trap

A value with one component greater than 12 can constrain that individual value. It does not establish the convention for every other row: the column may combine different exporters. Values such as 31/04/2026 are invalid rather than proof of a successful convention, and 03/03/2026 has the same date under either ordering.

Use a documented exporter locale or a source identifier with a verified contract. Timezones and fiscal periods can provide supporting context, but rarely establish a parsing convention by themselves. If both readings are plausible and no source evidence distinguishes them, retain the raw value and quarantine the row for review. There is no defensible fixed fraction of affected rows or guaranteed month shift.

pandas supports format="mixed" in versions that expose that option, but it infers each element; it does not resolve intent. dayfirst=True is a preference, not strict validation. See pandas.to_datetime. Explicit source-specific formats are easier to audit in a reporting pipeline.

Validate meaning, not just syntax

A zero NaT count means the conversion produced a value for every row; it does not prove semantic correctness. Keep parser status, reconcile counts, inspect source-specific examples, and apply documented business-date bounds or cross-field checks. Do not silently drop unresolved rows and change the report's population.

When you control the export, use an unambiguous year-first date representation and document whether values are calendar dates or timestamps, including timezone rules for timestamps. Preserve raw inputs beside parsed values. Sort only after the parsing policy is resolved; monotonic order after a sort is expected, not evidence that the policy was correct.

If you'd rather not rebuild this from scratch, I packaged the exact scripts I use: CSV Doctor — diagnose and repair a broken CSV before it corrupts your analysis. There is also a free sample if you want to see the format first.