CSV Date Formatting: How to Fix Ambiguous Dates

Why Dates Break

A date stored as plain text in a CSV carries no format metadata, "01/02/2024" is just a string until something decides how to interpret it. Different locales default to different orderings (MM/DD/YYYY vs DD/MM/YYYY vs YYYY/MM/DD), and a file moving between systems with different regional settings gets reinterpreted differently at every step, often without any error or warning.

Excel Makes This Worse, Not Better

Excel aggressively auto-detects and reformats anything that looks date-like when you open a CSV, sometimes converting an intended text value (like a version number "3/11") into an actual date, silently. This is one of the most common causes of quietly corrupted CSV data, values change before anyone edits anything, just from opening the file.

Defense: if a column shouldn't be touched, format it as text before opening in Excel, or avoid opening sensitive CSVs directly in Excel at all when precision matters.

The Fix: ISO 8601

ISO 8601 (YYYY-MM-DD) removes the ambiguity entirely: the year always comes first, so there's no region-dependent guessing. It's also the one date format that happens to sort correctly when treated as plain text, useful for anything that doesn't parse dates properly.

Handling Mixed Formats in One Column

Data merged from multiple sources often has genuinely inconsistent date formats within the same column, some rows "MM/DD/YYYY", others "DD-MM-YYYY", others already ISO. Standardizing this requires parsing each format correctly before rewriting, guessing wrong on an ambiguous date like "03/04/2024" silently swaps the month and day for some fraction of your rows.

Where the format is genuinely ambiguous and can't be inferred from context, flag it for manual review rather than guessing, a silent wrong guess is worse than a visible gap.

Including Time

For timestamps, extend the pattern: 2024-01-05T14:30:00Z, where the trailing "Z" denotes UTC. Storing timestamps without a timezone is its own ambiguity, especially for data spanning multiple regions.

Doing This in How To CSV

The Datetime Reformat tool standardizes mixed date formats to ISO 8601, and Date Decomposition splits a date column into separate Year/Month/Day/Weekday columns once it's clean, both run entirely in your browser.