How to handle ambiguous date formats in a CSV (03/04/2024 — March 4th or April 3rd?)
Why a date like 03/04/2024 means different things in the US vs. most other countries, and how to standardize it without silently swapping month and day.
Before you work through this by hand: you can check your own CSV free and see which of these problems your file actually has — duplicate rows, near-duplicates, broken quoting, encoding damage. It runs in your browser tab, nothing is uploaded, and there's no signup or email.
The ambiguity
The US commonly writes dates as month/day/year, while most other countries use day/month/year. "03/04/2024" is March 4th in the US convention and April 3rd almost everywhere else — and for any day-of-month 12 or under, both interpretations produce a valid-looking date, so nothing will error out even when it's wrong. Only dates with a day-of-month above 12 (like "25/04/2024") make the ambiguity obvious, and by then some rows may have already been silently misinterpreted.
The only real fix: know the source convention
There's no way to detect the correct interpretation purely from the data if every value is ambiguous (day ≤ 12) — you have to know which convention the exporting system used. Check the source system's locale/region setting, or ask whoever provided the file, rather than guessing.
Parsing explicitly once you know the convention
import pandas as pd; df = pd.read_csv('file.csv'); df['date'] = pd.to_datetime(df['date'], format='%d/%m/%Y') — specifying the exact format (here, day/month/year) forces pandas to parse consistently rather than guessing row by row, which is what happens if you let it auto-detect.
Why letting pandas auto-detect is risky
Without an explicit format, date-parsing libraries use heuristics that can infer different conventions for different rows in the same column if the data is inconsistent — better to fail loudly on a row that doesn't match your specified format than to silently parse it a different way.
Standardize to an unambiguous format for storage
Once parsed, export using ISO 8601 (YYYY-MM-DD) rather than re-exporting in a locale-specific format — it sorts correctly as plain text and has no month/day ambiguity for anyone downstream, tying into the same principle covered in our Excel date-handling guide.
See CSV Repair & Deduper (offline browser tool) — $19 one-time