Monthly CSV export from our booking system. The date column looks like 03/11/2025 in every cell, left aligned. Sorting puts 12/01 before 03/11. DATEVALUE works on some rows and throws #VALUE on others with no pattern I can see. Locale is set to UK. About 4000 rows a month, so I need something I can apply once and forget.
For a recurring monthly job, put the raw export on its own tab, untouched, and do all cleaning on a second tab with formulas. Then next month you paste over the raw tab and everything downstream updates. Cleaning in place means redoing the work every month, and eventually pasting into a half-cleaned sheet.
Doing this. I have been cleaning in place and it is exactly as fragile as you describe.
If you can change the import at all, that is the cheaper fix. In the CSV import dialog turn off convert text to numbers and dates, so nothing gets guessed, then convert deliberately with the formula above. Half the pain here is Sheets being helpful at the wrong moment.
Sanity check for anyone finding this later: select the column and look at the alignment before doing anything else. Right aligned means Sheets sees a real date or number, left aligned means text. Fastest diagnostic in the whole application and it takes no clicks.
@csv_apologist · 10mo ago · 3 replies
Left aligned means Sheets is storing them as text, so you are sorting alphabetically — "12" before "03" makes perfect sense to a string comparison.
The #VALUE pattern gives away the real problem: your export is almost certainly US format (MM/DD/YYYY) and your sheet is UK. Rows where the day is 13 or higher fail outright because there is no month 13. Rows where the day is 12 or lower silently convert to the wrong date, which is much worse — 03/11 becomes 3 November when it meant 11 March, and nobody notices until a quarterly total is off.
Parse it explicitly rather than trusting DATEVALUE:
=ARRAYFORMULA(IF(A2:A="","",DATE(INDEX(SPLIT(A2:A,"/"),,3), INDEX(SPLIT(A2:A,"/"),,1), INDEX(SPLIT(A2:A,"/"),,2))))That reads position 3 as year, 1 as month, 2 as day. Swap the last two arguments if your source really is day first. Then format the output as a date and sort on that.
Reply
Report
@northwindow_nia · 10mo ago
Checked a row whose real date I knew, and yes, it had silently flipped. That is an unsettling amount of wrong data sitting in last quarter's report.
Reply
Report
@till_and_tally · 10mo ago
This is why I ask a system owner for ISO dates whenever there is any option for it. 2025-11-03 sorts correctly even as text and cannot be misread by anyone.
Reply
Report