Blog

Insight

Why Bank Statement Dates in Your CSV Look Like 2024-07-01

Kittisak Ma

Image

Every bank prints dates in its own way, and most of them leave something out. Here is one transaction date from each of five statements in our test set, and what Docubrew writes for it:

Four of the five statements print no year. Bank of America prints two digits of one. Barclays puts the day first and spells the month. None of that would matter if every reader shared one convention, but 05/03/2022 is 3 May in New York and 5 March in London, and nothing in those ten characters says which.

Docubrew gets around this by writing every date in a CSV the same way: YYYY-MM-DD, the ISO 8601 order, year first. The rest of this post follows a date from the page to your file, including the time we got the day and month backwards.

One date format, whatever the bank

Docubrew's parsers used to write dates their own way. A Navy Federal file came out in ISO, Mercury spelled out the month, and Wells Fargo used US dates without leading zeros. Convert all three and your Date column held three formats.

Now the job is split in two. Each parser reads the dates exactly as its bank prints them, and one step at the end converts them all to the same format:

for parser in PARSER_REGISTRY:
    if any(parser.detect(text) for text in page_texts):
        result = parser.parse(pdf_bytes)
        apply_output_dates(result, parser.name)
        return result
for parser in PARSER_REGISTRY:
    if any(parser.detect(text) for text in page_texts):
        result = parser.parse(pdf_bytes)
        apply_output_dates(result, parser.name)
        return result
for parser in PARSER_REGISTRY:
    if any(parser.detect(text) for text in page_texts):
        result = parser.parse(pdf_bytes)
        apply_output_dates(result, parser.name)
        return result

That last step, apply_output_dates, rewrites every date column, not only the main one. When we made this change, we reconverted every statement in our test set and compared the results. 2,077 dates changed format, and nothing else did.

At first, Docubrew wrote every date as MM/DD/YYYY, the US order. Then we added UK and Australian banks, and to bookkeepers there 07/01/2024 is 7 January, not 1 July. The ISO date format YYYY-MM-DD reads the same way in every country, and it keeps rows in date order even when a program treats the column as plain text.

Importing the CSV into QuickBooks, Xero or Excel

QuickBooks Online and Xero both read the ISO column as dates when you import the CSV, so there's nothing to change.

Excel is the exception. It shows dates in your computer's own format, so 2024-07-01 may appear as 7/1/2024. That only changes how the dates look, but if you save the file again from Excel, they can be saved in that format too.

How Docubrew tells the day from the month

ISO fixes how we write a date. It does nothing for how we read one. A UK bank prints 05/03/2022, and before that becomes 2022-03-05 something has to decide that the 05 is the day.

Sometimes the dates give it away. 13/03/2022 can only mean 13 March, because there is no 13th month. But if every date on a statement falls between the 1st and the 12th, there's nothing to go on. So Docubrew doesn't guess from the digits. It goes by the bank, since a bank prints every statement in the same order. It keeps a list of the banks that print the day first:

_DAY_FIRST_PARSER_PREFIXES = (
    "barclays",
    "hsbc_uk",
    "lloyds",
    # ...
    "westpac",
)
_DAY_FIRST_PARSER_PREFIXES = (
    "barclays",
    "hsbc_uk",
    "lloyds",
    # ...
    "westpac",
)
_DAY_FIRST_PARSER_PREFIXES = (
    "barclays",
    "hsbc_uk",
    "lloyds",
    # ...
    "westpac",
)

Banks not on the list are read month first. Citibank is the one exception to the rule. Its statements from London print 20.06.2025, and its US-dollar accounts print 02.22.2025. So the parser works out the order for each statement and converts the dates itself.

The time we got the day and month backwards

BMO Bank, the US arm of Bank of Montreal, prints the month first, but it was on the day-first list. Every BMO date from the 1st to the 12th had its day and month swapped, so a statement period printed as 12/06/24 TO 01/05/25 came out as 12 June 2024 to 1 May 2025. Dates from the 13th onward can't be read day first, since there is no 13th month, so they stayed as printed. One column, two formats.

Bank statements that print no year

Most of the dates in the opening table have no year. Usually the year is printed once, in the statement period at the top, and every row borrows it.

That holds until the statement period crosses New Year. A cycle from 16 December to 15 January carries rows from two years, so Docubrew picks the year row by row, keeping whichever one puts the date inside the period. It also tries the year before, because a January statement can include a posting from late December. Without that, the row would be dated eleven months in the future instead of a week in the past.

Here's that rule in code:

candidates: list[datetime] = []
for year in range(period_start.year - 1, period_end.year + 2):
    try:
        candidates.append(datetime(year, month, day))
    except ValueError:  # e.g. 02-29 against a non-leap candidate year
        continue
inside = [d for d in candidates if period_start <= d <= period_end]
if inside:
    return inside[0]
candidates: list[datetime] = []
for year in range(period_start.year - 1, period_end.year + 2):
    try:
        candidates.append(datetime(year, month, day))
    except ValueError:  # e.g. 02-29 against a non-leap candidate year
        continue
inside = [d for d in candidates if period_start <= d <= period_end]
if inside:
    return inside[0]
candidates: list[datetime] = []
for year in range(period_start.year - 1, period_end.year + 2):
    try:
        candidates.append(datetime(year, month, day))
    except ValueError:  # e.g. 02-29 against a non-leap candidate year
        continue
inside = [d for d in candidates if period_start <= d <= period_end]
if inside:
    return inside[0]

Some statements leave the date off rows entirely. Royal Bank of Scotland prints a date only when it changes, and every row under it belongs to that day until the next date appears. Those rows take the date above them.

What we never do is make up a date the page doesn't print. One Barclays statement in our test set has three rows dated 31 Nov, and November has thirty days. Those rows reach your file as 31 Nov 2024, the day as printed with the year added, not as 30 November or 1 December. Likewise, a row printed as 29 Feb on a 2023 statement stays as printed, since 2023 had no 29 February.

When Docubrew doesn't have a parser for your bank

Every rule so far depends on knowing the bank. Docubrew first works out which bank a statement comes from (we explained how in How We Know Which Bank Your Statement Comes From).

When none of its bank parsers recognizes the statement, the fallback parser reads it. It used to read every numeric date month first. Here are four rows from a statement that prints the day first:

Docubrew used to write them like this:

Date,Description
2022-01-07,DUITNOW TO ACCOUNT <redacted> SALARY JUN 22 <redacted>
2022-02-07,MYDEBIT PURCHASE <redacted> MULTICARE PHRAMACY- TEMERLOH
2022-03-07,MYDEBIT PURCHASE <redacted> KS WALK IN MART-TMR TEMERLOH
13/07/2022,MYDEBIT PURCHASE <redacted> JOM MEOW MEOW HOUSE TEMERLOH
Date,Description
2022-01-07,DUITNOW TO ACCOUNT <redacted> SALARY JUN 22 <redacted>
2022-02-07,MYDEBIT PURCHASE <redacted> MULTICARE PHRAMACY- TEMERLOH
2022-03-07,MYDEBIT PURCHASE <redacted> KS WALK IN MART-TMR TEMERLOH
13/07/2022,MYDEBIT PURCHASE <redacted> JOM MEOW MEOW HOUSE TEMERLOH
Date,Description
2022-01-07,DUITNOW TO ACCOUNT <redacted> SALARY JUN 22 <redacted>
2022-02-07,MYDEBIT PURCHASE <redacted> MULTICARE PHRAMACY- TEMERLOH
2022-03-07,MYDEBIT PURCHASE <redacted> KS WALK IN MART-TMR TEMERLOH
13/07/2022,MYDEBIT PURCHASE <redacted> JOM MEOW MEOW HOUSE TEMERLOH

Every date from the 1st to the 12th had its day and month swapped, and the 13th stayed as printed. A statement covering July to November came out spread across all twelve months, in two formats, with no warning.

Now the fallback parser reads the order from the statement's own dates. One date like 13/07/2022, which only makes sense day first, tells it the whole statement is day-first. The same four rows now come out as:

Date,Description
2022-07-01,DUITNOW TO ACCOUNT <redacted> SALARY JUN 22 <redacted>
2022-07-02,MYDEBIT PURCHASE <redacted> MULTICARE PHRAMACY- TEMERLOH
2022-07-03,MYDEBIT PURCHASE <redacted> KS WALK IN MART-TMR TEMERLOH
2022-07-13,MYDEBIT PURCHASE <redacted> JOM MEOW MEOW HOUSE TEMERLOH
Date,Description
2022-07-01,DUITNOW TO ACCOUNT <redacted> SALARY JUN 22 <redacted>
2022-07-02,MYDEBIT PURCHASE <redacted> MULTICARE PHRAMACY- TEMERLOH
2022-07-03,MYDEBIT PURCHASE <redacted> KS WALK IN MART-TMR TEMERLOH
2022-07-13,MYDEBIT PURCHASE <redacted> JOM MEOW MEOW HOUSE TEMERLOH
Date,Description
2022-07-01,DUITNOW TO ACCOUNT <redacted> SALARY JUN 22 <redacted>
2022-07-02,MYDEBIT PURCHASE <redacted> MULTICARE PHRAMACY- TEMERLOH
2022-07-03,MYDEBIT PURCHASE <redacted> KS WALK IN MART-TMR TEMERLOH
2022-07-13,MYDEBIT PURCHASE <redacted> JOM MEOW MEOW HOUSE TEMERLOH

Whichever way Docubrew reads a statement, the dates in your CSV come out in the same YYYY-MM-DD format. If a statement ever converts with dates you don’t recognize, send it through the Report feature in the app and we’ll fix it for you, free of charge.

Every date, one format

A bank statement can print a date as 07/01/24, 9/9, Aug 28 or 30 Nov. Your CSV gets 2024-07-01, 2025-09-09, 2025-08-28 and 2024-11-30, ready to sort, filter and import. Behind that, a parser that knows how your bank writes its dates reads each one, the statement period fills in any missing year, and one final step writes every date the same way. When Docubrew doesn't have a parser for your bank yet, it reads the order from the statement's own dates and tells you whenever it had to assume.

To see which banks Docubrew supports, check the bank directory. If you want to know what the desktop app does between opening a PDF and exporting the file, What Happens Between Sign In and Export in Docubrew Desktop walks through it.