"Invalid date format" when importing bank transactions

Why the error appears at all

Your accounting software expects dates in one order. The file has them in another. Where the day is above 12, the mismatch produces an impossible month and the software says so: 25/04 read as MM/DD asks for the 25th month.

That is the whole of the visible error, and it is usually fixed by telling the import which order the file uses.

The half that produces no error

A date like 03/04 is valid in both readings. Read the wrong way it becomes a different, entirely plausible date, one month earlier or later. Nothing is flagged. The transaction lands in the wrong period, the month-end totals are wrong, and the only symptom is a reconciliation that does not agree.

Working out the real order

1. 1Scan the date column for any value where the first number is above 12. If you find one, the file is DD/MM.
2. 2If not, look for one where the second number is above 12. That makes it MM/DD.
3. 3If neither exists — a short statement where every date falls in the first twelve days — check the statement period printed on the PDF, which is usually written out in words.
4. 4Failing all of that, the bank’s country is the best remaining guide. It is a guess, and it should be verified against the running balance afterwards.

Fixing it in the file

The most reliable repair is to rewrite the column in ISO format — YYYY-MM-DD — which no software misreads. In a spreadsheet, do this with a formula into a new column rather than by reformatting the cells: reformatting changes only how the value is displayed, and the underlying misreading survives the export.

Then verify. Sort by date and confirm the first and last rows match the statement period on the PDF. If they do not, the order is still wrong.

Avoiding it entirely

• Prefer a format that states its dates unambiguously. OFX, QBO, QFX and Tally XML all do; CSV and QIF do not.
• Where CSV is the only option, write dates as YYYY-MM-DD.
• Set the date order explicitly on every import rather than accepting the default.
• Check the running balance after importing. A misdated transaction usually breaks it, which is what makes reconciliation worth doing.

The number format collides with it

Dates are not the only thing that varies by region. Much of Europe and Latin America writes 1.234,56 where the US and UK write 1,234.56 — the separators are swapped.

In a comma-delimited file this is not merely misread, it is structurally broken: 1.234,56 splits across two columns at the comma, and every column after it shifts by one. That usually produces a loud error, which is fortunate. The quiet version is a file where 1.234 is read as one point two three four.

A statement with European dates very often has European numbers too. Check both together rather than fixing one and importing.

Two-digit years

Some statements print 03/04/26. The software applies a cutoff to decide the century, and the cutoffs differ — most treat values under 30 as 2000s, but not all, and a statement genuinely from the 1990s can land a century out.

Expand two-digit years to four before importing. It is a find-and-replace, and it removes a class of error that is nearly invisible afterwards.