How to find inconsistent date formats in a spreadsheet

A date column is the easiest thing in a spreadsheet to get quietly wrong. Unlike a misspelled name, a misread date does not look like an error — it looks like a different, entirely plausible date. The checks below find the mix before something downstream acts on it.

1. Check whether dates are stored as dates or as text

This is the first thing to establish, because everything else depends on it. A spreadsheet stores a real date as a number and displays it according to the cell's format; a date that arrived as text is just characters that happen to look like a date.

The fastest check needs no formula at all. Select the column and look at the alignment: by default, real dates align right, like numbers, and text aligns left. A column with both is showing you the problem directly.

TRUE means a real date, FALSE means text. Run it down the column and count:

The reason this matters more than appearance: text dates sort alphabetically, not chronologically. In a text column "01/12/2026" sorts before "02/03/2025", which puts next December ahead of last March and produces a report that is wrong in a way nobody notices.

2. Look at the range of formats present

Once you know the column is mixed, find out what it is mixed between. A column assembled from several sources typically holds three or four distinct shapes:

Only the slash formats are genuinely ambiguous — and they are the most common.

Anything with a named month or a leading four-digit year can be read with confidence. Your attention belongs on the slash-and-dash rows.

3. Prove the day/month order from the data

Faced with "03/04/2026", the instinct is to guess from context — the file came from an Australian system, so it must be day-first. Resist it, because the column can usually prove the answer.

A single source may use one consistent date order, but a combined export can contain both. Count evidence for day-first and month-first formats, and confirm the source convention before applying either interpretation to ambiguous values.

The FIND("/",...) is what keeps these honest rather than just locating the separator. Anything without a slash — a serial number like 46115, an ISO date, an empty cell — makes FIND fail, IFERROR turns that into 0, and the value is excluded instead of being misread. A naive LEFT(A2,2) would read the first two digits of 46115 as 46, count it as a day above 12, and report day-first evidence that is not there.

A decisive value is evidence for its source format, not a guarantee that every row follows it.

If the count comes back zero, the file genuinely cannot tell you and no amount of staring will change that. Check the exporting system's regional settings, or ask whoever produced the file. Guessing wrong silently corrupts every date where the day is 12 or lower — which is roughly two in every five rows, all of them still looking perfectly reasonable afterwards.

4. Watch for two-digit years

A two-digit year adds a second ambiguity on top of the first, and spreadsheets resolve it with a cutoff rule rather than knowledge:

The cutoff sits at 30 in Excel's default: 00–29 becomes 2000–2029, 30–99 becomes 1930–1999.

For dates of birth this is often what you want. For a signup date or a renewal date it silently sends records a century into the past, and a filter for "this year" then returns nothing. Find them with a length check on the year portion, and if the column has any two-digit years at all, fix the year before you fix anything else.

5. Check for more than one separator

Mixed separators are the visible symptom of a column assembled from several sources, and worth listing even when they are not themselves harmful:

Separators alone do not establish the source convention. Rows from different systems can use the same separator but opposite day/month order. Check source documentation or split records by known origin before converting ambiguous dates.

6. See the whole mix at a glance

To find the outliers without scrolling, use conditional formatting rather than reading:

Select the column, then Home → Conditional Formatting → New Rule → Use a formula, and enter =AND(A2<>"",NOT(ISNUMBER(A2))). Every text-stored date highlights at once, and the pattern is usually immediately informative — a contiguous block means one bad import, scattered singles mean hand entry.

The A2<>"" is not decoration. An empty cell is not a number, so NOT(ISNUMBER(A2)) alone is true for every unused row below your data and highlights the rest of the column as though it were full of broken dates.

What to standardize to

Once the column is consistent, ISO 8601 (YYYY-MM-DD) is the format worth defaulting to for anything that will be sorted, filtered, or imported: it is unambiguous in every locale, and it sorts correctly even as text, which removes the entire class of problem in section 1. Use a local display format for a report a person will read, and keep ISO for the file a machine will consume.

Common questions

Why did my dates turn into five-digit numbers? That is the underlying serial number showing through because the cell's format was reset to General. The data is intact — set the cell format back to Date and it displays correctly again. What serial 1 means depends on the workbook's date system: 1 January 1900 in the 1900 system, which is the default nearly everywhere, and 2 January 1904 in the 1904 system, which some workbooks created on older Macs still use. A file that reads four years and a day out is usually this rather than a data error.

Why does the same file show different dates on a colleague's machine? Because a text date is interpreted using each machine's regional settings. The file is identical; the reading of it is not. Converting to real dates, or to ISO text, makes it machine-independent.

Can I just use Text to Columns to fix a date column? Yes, and it is the fastest manual fix — Text to Columns → Next → Next → Date, then pick the order that matches your column. It only works if the whole column shares one order, which is what section 3 establishes.