I have just exported my transaction history to CSV so that I can do up a bit of a personal budget based on my spending habits over the last year. On importing into Google Sheets I was struggling to sort the data by the date row so I looked into the raw CSV data and noticed that the format of the date is inconsistent!
- 99% of the data is in the correct date format (or at least the same date format) - dd/mm/yyyy
- About 1% of the data is for some reason in the US format of mm/dd/yyyy
- Locale of my spreadsheet is set to UK, which allows Sheets to recognise UK format for date from the source date.
- This is not an issue with Format > Number > Date; it’s the source data that has the errors and changing the date format only served to correctly identify the wrong 1% while then formatting the other 99% incorrectly.
- The incorrect data in question seems to all be recent, within the last month, as if there was a format change on January 3rd 2021, that was then changed back on Jan 10th 2021. But that could just be my segment.