Guides › Day/month date confusion
Bank CSV dates look wrong: day/month or month/day?
A date like 05/03/2021 is 5 March in most of the world and 3 May in the US style. A bank CSV does not say which one it uses, and a spreadsheet can guess wrong without telling you. Wrong dates put spending in the wrong month, which ruins monthly totals.
How to tell which one your file uses
- Open the CSV in a plain text editor (Notepad, TextEdit), not in a spreadsheet.
- Scroll the date column and look for any date where the first number is above 12 (for example 27/03/2021) or the second number is above 12 (03/27/2021).
- A first number above 12 means day first. A second number above 12 means month first.
- If every date in your file has both numbers 12 or below (a short export from the first half of a month), you cannot tell from the dates alone. Compare one line against your banking app, where you know the real date of a purchase.
How spreadsheets get it wrong
When you double-click a CSV, Excel interprets dates using your computer's regional settings. A day-first file opened on a month-first computer can turn 05/03/2021 into 3 May, while 27/03/2021 stays as text because there is no month 27. The result is a column that is partly real dates and partly text, with no warning. Importing with Data › From Text/CSV lets you check and set the data type and locale for the column before it loads.
What the categorizer does
It detects the date column, and when a file is ambiguous (for example when every date could be read either way) it asks you which order the dates are in, instead of guessing silently. If you answer, it uses your answer for the whole file.
Check before you trust the totals
- Compare the first and last transaction dates in the tool with your bank app.
- Check that the number of months in the monthly totals matches the period you downloaded.
Source and honesty note
This page is our own explanation, not a bank help page. The Excel behaviour is general and we describe it from knowledge; we did not test it for this page. The categorizer's question about ambiguous dates is described on our home page.
Then categorize it free, offline
Drop your bank CSV on the page. It reads the file inside your browser tab and does not upload it.
Free: one file at a time, latest 3 months of it. Need the CSV first? See the bank export guides.