Established onExcel 365, Windows and Mac: locale parsing on entryExcel: 1900 and 1904 origins, per workbookGoogle Sheets, web: 1899-12-30 origin
A date stored in a cell is a number counting days from an origin, and it is unambiguous. The trouble is entirely at the boundary, when a date arrives as text and something has to decide what it means.
The ambiguity
Three slash four slash twenty twenty-six is the third of April or the fourth of March, and which one depends on the settings of the machine reading it. Only dates whose day exceeds twelve disambiguate themselves, which means roughly two thirds of dates in any file are ambiguous and the rest are not.
That distribution is the reason the problem survives testing. A sample of a few rows very often contains a day above twelve, parses correctly, and gives no indication that the rest of the file will not.
The written form that solves it
Year, month, day, in descending order of significance, separated by hyphens. It is unambiguous in every locale, it sorts correctly as plain text without being parsed at all, and it is the format defined by the international standard for the purpose.
Where the choice of format is yours, this is the answer, and there is no second-best.
The two origins
Excel counts from the start of 1900 by default and offers a setting that counts from the start of 1904, which was the default on early Macintosh versions. A workbook using one, opened alongside one using the other, shows dates differing by four years and a day.
The setting is stored per workbook, so copying dates between two files with different settings shifts them, silently, by 1462 days.
The deliberate error in the count
Both products treat 1900 as a leap year, which it was not, to remain compatible with a spreadsheet from the early nineteen-eighties that had the defect. Dates before the first of March 1900 are therefore off by one day.
It is documented by the vendor as intentional and it almost never matters. It is worth knowing as the clearest case in this subject of a defect deliberately preserved for forty years because too much depended on it.
The concept that does not exist
There are no time zones. A timestamp is a number of days and a fraction, and nothing anywhere records which zone it was taken in.
So a file combining timestamps from systems in different regions contains values that cannot be compared, and nothing about them says so. This is the largest silent error available in this part and it has no remedy inside the spreadsheet: the zone has to be carried as a separate column, by agreement, or the times converted before they arrive.
What to do on import
Declare the column as a date and state the order the text uses, which the import route in both products allows and the open-by-double-click route does not. Where the order cannot be declared, bring the column in as text and convert it with a formula that takes the parts by position, which is verbose and cannot be misread.
How to check afterwards
Two cells. The minimum and the maximum of the date column: a date in 1905 or in 2087 in a file of last quarter's transactions is a parse that went wrong, and it is visible instantly.
And a count of the values in the column that the sheet does not consider dates, which finds the rows that failed to parse at all and were left as text. Those are the fortunate ones, because they are visible.
The rows that parsed wrongly and look fine
Where a file uses day-month order and is read as month-day, every row with a day above twelve fails to parse and stays text, and every row with a day below thirteen parses into the wrong date and looks perfect.
So a column half text and half dates is not a partial failure. It is a complete failure in which the visible half is the part that worked correctly, and the invisible half is wrong. Anybody repairing such a column by fixing the text entries has repaired the wrong half.
What we cannot verify
The origins, the setting, the deliberate leap year and the absence of time zones are all documented by the vendors and demonstrable directly. The international standard for the written form is published. Locale parsing behaviour was confirmed on current builds of both products; behaviour in older versions is taken from documentation.
The other place the ambiguity appears
Not only on import. Typing a date into a cell parses it against the same settings, so a colleague entering dates on a differently configured machine, in a shared workbook, produces values that look identical on screen and differ by months.
The remedy is the same and easier to enforce here: a validation rule that requires the written form, which the entry on validation in the final part covers, converts the problem into a refusal at the point of entry rather than a discovery afterwards.
In short
- A stored date is a number and is unambiguous; the trouble is at the boundary with text.
- Only days above twelve disambiguate, so a sample very often parses correctly by luck.
- Year, month, day with hyphens is unambiguous everywhere and sorts as plain text.
- Two origins exist, and copying between workbooks using different ones shifts dates by 1462 days.
- There are no time zones anywhere, so timestamps from different regions cannot be compared.
- A column half text and half dates has failed completely; the visible half is the part that worked.