What you are seeing
Invalid date format in row 14. Expected DD/MM/YYYY.
An invalid date error is a gift. It means the software noticed. The same underlying problem, on a different row, produces no error at all, and that is the case worth understanding, because it is the one that reaches your books. Every importer words the message differently, but the causes are the same handful, and so are the fixes.
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 month-first asks for the 25th month. Xero’s version of this is a refusal to read the file at all; QuickBooks, Sage and Wave reject the file or the row in their own words. The causes, most likely first:
- Day-first dates imported as month-first, or the reverse. By far the commonest, and the only one with a silent half.
- Mixed formats in one column, usually because Excel converted some dates and left others as text when the file was opened.
- A date pattern the importer does not take at all: ISO dates where slashes are required, a month written as a word, or no separators.
- Missing years. Many card statements print 15 Mar with no year, and a converted file inherits the gap.
- Two-digit years, read with a different century cutoff than you expect.
- Stray text in the date column: a header repeated at a page break, a “Balance brought forward” line, or a blank row.
A converter that works out the date order from the statement itself, by looking for a day above 12 somewhere in the file, removes the ambiguity before it reaches your accounting software.
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, months away. 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.
How much of a statement this affects
Roughly 40% of the days in any month fall on the 12th or earlier, so a statement imported with the wrong order typically has around 40% of its rows silently misdated and the rest rejected. A partial rejection is therefore a strong hint that the rest of the file is wrong too, not a hint that only those rows had a problem.
Some importers make this worse by remembering. Xero asks you to confirm an ambiguous date format and then reuses it for future CSV statements with the same headers, so a wrong choice made once can misdate every later import without any prompt.
What each importer expects
There is no universally safe CSV date. The table below is drawn from each vendor’s own help; where an importer lets you choose, choosing is the fix.
| Software and file | Date format | Can you choose? |
|---|---|---|
| Xero CSV | DD/MM/YYYY, MM/DD/YYYY, YYYY/MM/DD and variants | Yes; asked when ambiguous, then saved |
| QuickBooks Online CSV | One format throughout the file | Yes, at the mapping step |
| Sage three-column CSV | Slash-separated dates; ISO with dashes not accepted | Yes: day, month or year first |
| Wave CSV | YYYY/MM/DD or YYYY-MM-DD, not in the future | No |
| Quicken for Windows CSV | mm/dd/yyyy | No |
| MYOB QIF | Short dates; MYOB asks for dd/mm/yy | No, choose at download |
| Tally XML | YYYYMMDD | No; anything else is rejected |
| OFX, QFX, QBO | YYYYMMDD inside the file | Not needed; unambiguous |
Working out the real order
- 1Scan the date column for any value where the first number is above 12. If you find one, the file is day-first.
- 2If not, look for one where the second number is above 12. That makes it month-first.
- 3If neither exists, because every date falls in the first twelve days, check the statement period printed on the PDF, which is usually written out in words.
- 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.
Decide once for the whole statement. A file is never half day-first and half month-first unless something has edited it, and that something is usually Excel.
When Excel has already changed your dates
Double-clicking a CSV opens it in Excel, which reads every date using your computer’s regional setting. Dates that fit become real dates; dates that do not fit stay as text. Save the file and Excel writes the converted ones back in its own format, so a clean file can come out mixed.
The tell is alignment. Real dates sit on the right of the cell; text sits on the left. A column where the early-month rows are right-aligned and the rest left-aligned has been half converted, and the right-aligned ones are probably wrong.
- 1Go back to the original CSV, not the saved copy.
- 2Open it from inside Excel with Data → From Text/CSV, and set the date column to Text, so nothing is converted.
- 3Build a new column with a formula. For day-first text in A2: =DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)).
- 4Format that column the way the target software wants, check it, and paste it over the original as values.
Fixing it in the file
Where the importer accepts it, ISO format (YYYY-MM-DD) is the most reliable repair, because it has only one reading. Check the table first: Wave asks for it, Sage’s three-column CSV rejects it, and Quicken’s CSV wants mm/dd/yyyy. Do the rewrite with a formula into a new column rather than by reformatting the cells: reformatting changes only how a value is displayed, and a misread date stays misread underneath.
Then verify. Sort by date and confirm the first and last rows match the statement period on the PDF. Count the rows in each month against the statement. If either disagrees, the order is still wrong somewhere.
Missing years, and December to January
Credit card statements in particular often print the day and month only, with the year in the statement period at the top. A statement running from 18 December to 17 January contains two years, and a conversion that stamps every row with one of them puts half the transactions twelve months out. Excel does the same when it turns 15 Mar into a date, using the current year.
Check the rows either side of 1 January on any statement that spans it. The year should change exactly once, going forward.
Two-digit years
Some statements print 03/04/26. The software applies a cutoff to decide the century, and the cutoffs differ, so an old statement can land a century out. Expand two-digit years to four before importing. It is a formula, and it removes a class of error that is nearly invisible afterwards.
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. In a comma-delimited file that 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. 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.
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 in the one form your importer documents, and keep it the same every month so a saved mapping stays right.
- Set the date order explicitly on every import rather than accepting the default.
- Never open and re-save a statement CSV in Excel before importing it.
- Check the running balance after importing. A misdated transaction usually breaks it, which is what makes reconciliation worth doing.