Mixed date formats

• The column was filled in by several people, each writing dates the way they learned to.
• Rows were merged from systems that export dates differently.
• A spreadsheet recognized some cells as dates and reformatted them, and left the rest as typed text.

• Sorting puts rows in text order, so the tenth of a month can land before the second.
• Date filters and date arithmetic silently skip every value they cannot read.
• Importing into a database fails on the first row that does not match the expected style, or worse, loads it as empty.

• Sort the column and read the first and last twenty values. Mixed styles cluster at the ends.
• Count values by length. A clean date column has one length; a mixed one has several.
• Look for separators: slashes, dashes, dots, and spelled-out months in the same column.

• Choose one output style for the whole column. Year first (ISO 8601) sorts correctly as plain text and is read the same way everywhere.
• Parse each value with the style it was actually written in, then write it back in the chosen style.
• Set aside any value that could be read two ways instead of guessing. See ambiguous day and month order.

• Ambiguous day and month orderA date such as 03/04/2025 could mean March 4 or April 3, and nothing in the cell says which.
• Numbers stored as textA column of quantities contains symbols, separators, or words that stop software reading it as numbers.
• Case example: Non-profitsA donor list that counted the same people twice

TermsISO 8601, Parsing, Standardization