Is 03/04 March or April? Resolve ambiguous bank CSV dates

Do not let the computer’s locale decide what 03/04 means in a bank export. Confirm the file’s date convention using the statement and an unambiguous date, then apply that rule consistently. A date that parses successfully can still be wrong.

A date-format decision table for bank CSV imports

The following strings are invented test cases, not transactions from a customer. Establish the rule before importing the real file; each row answers a different question.

Raw date	What the string establishes	What you still need to confirm
03/04/2026	Two valid interpretations: 3 April or 4 March	The convention for this source column
17/04/2026	Cannot be month/day/year	That the other rows use the same convention
04/17/2026	Cannot be day/month/year	That it belongs to a separate month-first source
31/04/2026	Invalid in both conventions	The source reading; swapping parts will not repair it
03/04	Day/month ambiguity and no year	Column convention plus the statement’s dated period

Do not infer a date format from the website language or the account currency. An English-language statement can use day-first dates; two exports from different systems can use different formats even for the same account. A file with both 17/04/2026 and 04/17/2026 needs investigation, not an automatic majority vote.

Practical method

1. Keep raw date text. Find a source value such as 17/04 that distinguishes day-first from month-first, and read the period heading.

2. Parse with an explicit format and culture rather than automatic detection. Treat files with different conventions separately.

3. Compare parsed day, month and year with the original for several rows. Flag dates outside the statement period without silently changing them.

Fictional example

A fictional April statement contains 03/04/2026 and 17/04/2026, confirmed as day/month/year. The first becomes 2026-04-03, not 2026-03-04. If another file uses month/day/year, do not append its raw strings and reuse April’s rule.

Establish a rule for the file, not just one plausible row

Record the source file, exact date-column heading, raw string, confirmed convention and parsed date. A value such as 17/04/2026 rules out month-first for that value, but extends to 03/04/2026 only after confirming the same column uses one convention. Do not let a date embedded in the description override the booking-date column. If two merged exports disagree, recover their file identities and parse them separately.

If every date has day and month at most 12, seek the bank’s export definition or an unambiguous counterpart in the statement. A period covering both March and April cannot decide 03/04 by itself. Leave the date unresolved if the evidence cannot distinguish the alternatives. Successful conversion is not additional evidence.

Convert from preserved text and verify the reporting month

For the confirmed day-first fictional file, an explicit Power Query expression is Date.FromText("03/04/2026", [Format="dd/MM/yyyy", Culture="en-GB"]). The intended date is 3 April 2026. Choose the date pattern and locale from the confirmed source convention; automatic parsing cannot supply evidence for an absent year. If an earlier spreadsheet import has already changed the date, restart from the untouched CSV text rather than merely changing the cell’s display.

Give the fictional row a debit of 28.40. Reading it as 4 March moves that debit into March instead of April, while the sum over both months stays 28.40. Check the month allocation as well as the amount total. Keep the raw date beside the parsed date when sorting, so this disagreement remains visible.

Import into Excel without losing the evidence

Work on a copy of the CSV and import through the text/CSV data workflow. In Power Query, inspect the applied steps: if an automatic type conversion has already interpreted the date column, remove or revise that step before proceeding. Preserve the source as text, duplicate the column for conversion, then choose the date type with an explicit locale. Keep the source format separate from interface language, and choose conversion settings explicitly. Menu wording varies with Excel version and language.

A simple review sheet has six fields: file identifier, source row, raw date, confirmed rule, parsed date and review status. Add extracted year, month and day to the parsed column when the displayed format is unclear. Sorting a date-looking text column is not a substitute for parsing: 12/01/2026 can sort after 02/12/2026 as text even when January should come first.

Handle missing years and test the exported file again

Consider a wholly fictional statement covering 20 December 2026 to 19 January 2027, with rows 28/12 and 03/01. Once day-first order and the row context are established, the intended years are 2026 and 2027 respectively. Appending the current year to every row would be wrong. If the period covers several possible occurrences of the same day and month, leave the year unresolved rather than reconstructing a chronology that merely looks plausible.

Before handing off the CSV, check an ambiguous date, an unambiguous date, a month boundary and a missing-year case if present. Reopen a copy through the receiving application’s import preview and compare the interpreted values with your review sheet. Writing 2026-04-03 reduces ambiguity for people, but the receiving system must still support that layout. A correct date display in Excel does not prove that the next importer has kept the same value.

Checks and limits

Test an ambiguous date and an unambiguous one. A validity check catches impossible dates but cannot detect every swapped day and month. Preserve the original strings after conversion. If the period and row conflict, request clarification from the source rather than choosing the interpretation that fits a report.

Keep the original file unchanged and record any unresolved item before using the table.

Related checks

Choose the documented date column and period before parsing an ambiguous date. Booking date and value date: keep both in a bank statement export · Overlapping bank exports: review duplicates without losing payments · Check a bank CSV before opening it in Excel.

BankStatementScan is in preparation. The site offers an access request and a fictional example; it does not currently convert your bank files. See the planned service.

Sources consulted on 16 October 2026

• RFC 3339 — Dates and timestamps.
• W3C — Metadata Vocabulary for Tabular Data.