You paste 03/04/2024 into a Google Sheet meaning 3 April. Sheets stores 4 March. Nobody notices for a year, and by then the wrong date has been exported into a CRM, mailed to customers, and used in a report.
This is not a bug you can argue with. It is how spreadsheets work, and it is worth understanding properly, because the usual fixes make it worse.
What is actually happening
A spreadsheet does not store 03/04/2024 as the text you typed. On entry it parses that text into a date value, using the spreadsheet's locale - not yours, and not the source system's.
If the file locale is United States, 03/04/2024 is read month-first as 4 March. If it is United Kingdom or Australia, it is read day-first as 3 April. Same characters, different stored value, no warning either way.
The damage is done at the moment of entry. Once the text has been parsed, the original string is gone, and reformatting the column afterwards only changes how the already-wrong value is displayed.
Why the obvious fixes do not work
"I changed the number format to yyyy-mm-dd." Formatting changes presentation, not the underlying value. A date parsed as 4 March will display as 2024-03-04. Tidier, still wrong.
"I set the file locale correctly, then re-imported." This does help - but only for rows imported after the change. Anything already in the sheet was parsed under the old locale and is unaffected.
"I used find and replace." You cannot pattern-match your way out of this. 05/06/2024 is ambiguous; 25/06/2024 is not. A blanket rule silently corrupts one of those two groups.
The one reliable fix: import as text first
Set the file locale before anything else, on an empty sheet: File, then Settings, then General, then Locale.
Format the destination column as plain text before pasting or importing: Format, then Number, then Plain text. Text is not parsed, so the characters you typed survive intact.
Import, then convert deliberately. Convert the text to real dates yourself, applying the convention you know the source system used, rather than letting the spreadsheet assume one.
Keep the original column. Convert into a new column so the raw text is still there to check against.
If the data is already in the sheet and already parsed, there is no way to recover the original text from the sheet alone. Go back to the export and start again with the steps above - that is genuinely faster than auditing the damage.
The part most tools get wrong
Some dates simply cannot be resolved from the value alone. 03/04/2024 is one of them. Any tool that converts it without telling you has not solved your problem; it has hidden it.
In a mixed export, roughly a third of day/month pairs are ambiguous - every combination where both numbers are 12 or below. The rest, like 25/12/2024, can only be read one way and are safe to convert automatically.
So the correct behaviour is: convert what is unambiguous, apply a stated convention to the rest, and flag every row where the convention had to be assumed so a human can spot-check a sample. That is a much smaller job than checking everything, and it is the difference between a clean file and a file you merely hope is clean.
A worked example
From a real run on a messy contact export:
2024-03-12 stays exactly as it is. It is already unambiguous ISO.
25/12/2024 becomes 2024-12-25. There is only one valid reading, so it is converted silently.
12 Mar 2024 becomes 2024-03-12. The month is named, so there is no ambiguity.
03/04/2024 becomes 2024-04-03 and is flagged NEEDS REVIEW, with the reason recorded as "assumed day/month".
2024-13-45 is left exactly as it was, and flagged. It is not a date, and inventing one would destroy the evidence that the source is broken.
If you would rather not do this by hand
We clean contact and CRM data to exactly the rules above - unambiguous values converted, assumptions flagged, and every change written to a log showing the before value, the after value and the reason. Nothing changes without a log entry.
Email challenge.ops.2026@gmail.com with a couple of example rows for a fixed quote. Please do not send a full customer database in a first email - a handful of rows is enough.
Daniel Muguira. ABN 30 160 515 078. Queensland, Australia.