The same monthly CSV imports with half the dates as text and half silently swapped day and month — how do I stop guessing?

Fix it in the import step, where you can state the format explicitly instead of hoping.

In Excel: do not open the CSV. Use Data → Get Data → From Text/CSV, which opens a preview where you set the type of each column, and critically lets you choose Using Locale on the date column so you can say "this data is day-first" regardless of what your machine is set to. That single dropdown is the whole fix. Once the query is saved, next month is a refresh and it parses identically — which also solves the real problem, that this is a recurring file and a manual fix has to be done correctly every time forever.

In Google Sheets: File → Import and turn off automatic type conversion, so everything arrives as text and nothing has been guessed. Then convert deliberately with a formula — pull the parts out with SPLIT or the text functions and reassemble with DATE(year, month, day), where you are the one deciding which part is which. Verbose, and it cannot be wrong.

Either way the principle is the same and it is the general lesson: parse explicitly, or arrive as text and convert deliberately. Automatic conversion of ambiguous input is convenient right up until it is silently wrong, and there is no way to audit it afterwards.

You have found the nastiest bug in spreadsheets, and the detail you noticed is the diagnosis: only days of twelve or under are wrong.

That happens because the importer is reading DD/MM as MM/DD (or the reverse). When the first number is over twelve it cannot be a month, so the importer gives up on its assumption and either swaps to the right interpretation or leaves the value as text. When the first number is twelve or under, both readings are valid, so it silently takes the wrong one and produces a real, plausible, wrong date.

That is why your file splits three ways, and it is why this is so dangerous: the failure is invisible on exactly the rows where it happens, and the rows that stayed as text are the ones that saved you, because they were the visible symptom that made you look.

So the rule that follows: never let the spreadsheet guess. Any solution that involves opening the CSV by double-clicking it is unfixable, because the guess happens before you can intervene and you cannot tell afterwards which rows were guessed. Reformatting the column after the fact does not undo it either — the wrong date is now genuinely stored as the wrong date.

The fix is always to control the parse at import time, which the next answers cover. And for what it is worth: yes, you should assume earlier months are affected. Anything with a day of twelve or under is suspect.

Two things that turn this from a monthly hazard into a non-event.

Ask the sender for ISO format. 2026-07-03, year first. It is unambiguous, every tool on earth parses it the same way, and it sorts correctly as plain text even if nothing converts it at all. It is very often a one-line change in whatever generates the export, and people say yes far more often than you would expect because it costs them nothing. This is the highest-value thing in this thread and it is a five-minute email.

Add a check that fails loudly. Whatever the import method, put a formula somewhere that counts rows outside the range you expect — dates before the start of the month or after the end of it. If the day and month get swapped again, that count goes non-zero immediately and you see it before the report goes out, rather than a month later because a total looked odd.

One more habit worth having: keep the original CSVs somewhere untouched. When you do discover a problem like this, being able to re-import six months correctly is the difference between an afternoon and a reconstruction. If you still have the raw files for the earlier months, you can re-run them properly and find out how bad it was rather than wondering.