Why Your QIF Import Lands a Month Out

01/02/2024 is 1 February or 2 January depending on who exported the file, and a QIF carries nothing that says which. How to tell which one you have before importing a year of wrong dates.

You import a QIF. Everything looks right — the payees are right, the amounts are right, the total is right. Months later you notice a January transaction sitting in March.

The dates were ambiguous and something guessed.

The problem in one line

01/02/2024 is the 1st of February in most of the world and the 2nd of January in the United States. A QIF file contains nothing that declares which convention it was written in.

Not a header, not a locale field, not a version marker. The format was designed in the United States when that was not a question anyone was asking.

When the file can answer, and when it cannot

Sometimes the file settles it by accident. If any date anywhere has a first number above 12 — `25/12/2023` — then the first number cannot be a month, so the file is day-first and every date in it can be read that way with confidence.

The same works in reverse: `12/25/2023` proves month-first.

The dangerous case is a file where no date has a number above 12 in either position. A statement covering the first twelve days of a month. A quiet account with four transactions. A filtered export. Nothing in the file settles it, and both readings are equally consistent.

What should happen instead

Two things, in order. Resolve it from the file where the file can settle it — scanning every date, not just the first one. And where the file genuinely cannot settle it, say so, state which reading was used and why, and tell the reader to check one date before trusting the rest.

That is what our QIF to CSV, QIF to Excel and QIF to QBO converters do. If your file is ambiguous you are told, in the conversion notes, before you download anything.

The same ambiguity exists in CSV exports, for exactly the same reason, and CSV conversions report it the same way.

Checking a file yourself

1. Open the QIF in a text editor It is plain text. Dates are the lines beginning with D.
2. Scan for a number above 12 Look at the first number of each date. If any is 13 or higher, the file is day-first and you are safe.
3. If none is, check the second number If any second number is 13 or higher, it is month-first.
4. If neither, find one you recognise Pick a transaction you remember — rent, a salary, a large purchase — and check its date against your statement. One known date settles the whole file.

What to do when it is already wrong

If you have imported a year of dates a month out, do not fix them by hand. Delete the imported transactions and reimport from a corrected file — hand-editing is how you end up with some corrected and some not, which is worse than uniformly wrong because it is no longer detectable by pattern.

Frequently asked questions

Does the QIF header say anything about dates?

No. !Type:Bank declares the account type, not the date convention. There is no field for it in the specification.

What about two-digit years like '24?

Quicken wrote those too. They are less dangerous because the year is rarely confusable with a day or month, but they still need interpreting against a century.

Is OFX affected by the same problem?

No. OFX dates are YYYYMMDD, which cannot be misread. It is one of the reasons to prefer the OFX family where your software accepts it — see which format your software wants.

Can I just tell the converter which order to use?

Where the file proves its own order, that is used and your preference cannot make it wrong. Where it does not, you are told — which is the point at which you check one date and know.

Convert a statement now

Convert a bank statement PDF to Excel, or see every format. More in the guides.