Dates in more than one format, and why it is worse than it looks.
A date column in two formats silently misplaces rows in every period comparison, and about a third of them cannot be recovered at all.
- Read6 min
- Sections4
How do I fix mixed date formats in a spreadsheet?
First establish which rows are in which format, because a column mixing day-first and month-first dates cannot be repaired with one format string. Below the thirteenth of the month the two are indistinguishable, so those rows cannot be recovered from the data alone. Find the rows above it, use them to establish which format the file was written in, and get the source re-exported as ISO.
01
Which date rows cannot be fixed at all?
03/04 is the third of April or the fourth of March, and the file does not say which. If a column contains both day-first and month-first dates, every value where both parts are twelve or below is ambiguous, and no amount of processing recovers the intent.
That is roughly a third of a year's dates. Any tool that silently picks one interpretation for those rows is making up about a third of your time series.
So the first job is not repair. It is establishing how many rows are ambiguous, because that number decides whether the column is usable at all.
Any value with a component above twelve is unambiguous. Those rows are the evidence for what format the rest are in.
02
How do you tell whether dates are day-first or month-first?
Take every value where one component exceeds twelve. If they are consistently day-first, the file was written day-first, and the ambiguous rows are almost certainly the same. If both patterns appear among them, the column genuinely contains two formats and probably two sources.
Two sources in one column is the more common cause: an export that was appended to by hand, or two regional systems writing into the same sheet. That is worth knowing regardless of the dates, because everything else in those rows came from two places too.
03
Why are my dates before March 1900 off by one day?
Excel treats 1900 as a leap year, because Lotus 1-2-3 did and compatibility won. Every date before the first of March 1900 is off by one day, which matters for historical records and almost nothing else.
The one that does bite is the epoch: workbooks saved on older Macs count from 1904 rather than 1900, so the same serial number is four years and a day apart depending on the file. Combining two workbooks with different epochs produces a date column that is wrong in a way that looks plausible.
04
How do you stop date problems coming back next month?
Ask the source system to export dates as ISO 8601, which is unambiguous by construction and sorts correctly as text. It is a one-line change in most export dialogues and it removes this entire class of problem permanently.
Where you cannot change the export, at least record which format you decided the file was in and how many rows you could not resolve. That number belongs next to any conclusion drawn from the column.
See it on a real project
Two clients, 38% of revenue, accounted for every one of the 38 overdrawn days.
Is winter actually our slow season?Related
Try it on your own file.
Every check described here runs automatically, and your first findings arrive in full before you pay for any of it.
No account needed to start. You only pay when you like what you see.