Fix ambiguous dates in a bank statement CSV

Separate interpretation from presentation

The text 04/05/2026 can mean April 5 or May 4. Once a spreadsheet has chosen a meaning, changing the cell’s display to a different date format may simply display the same wrong date differently. Return to the original text before deciding how to correct it.

Keep a Raw date column in a working copy. Use a separate Parsed date column for the result you are checking. That separation lets you see the transformation instead of overwriting the evidence that explains it.

Establish the source convention from evidence

Read the statement heading, date labels and bank export instructions. A value such as 23/04/2026 rules out month/day ordering for that row, but it does not prove that files from another account or institution use the same convention.

For a fictional April statement containing 04/05/2026 and 04/23/2026, the second date supports month/day ordering if both rows belong to the same consistently formatted export. If every date is ambiguous, use the source PDF or ask the issuer rather than inferring the order from your computer’s regional settings.

• Check the year, especially in files that span December and January.
• Distinguish a transaction date from a posting date if both appear.
• Treat inconsistent source formats as separate groups for review.
• Do not assign today’s year to a month/day value merely because the year is absent.

Import with the confirmed date order

Microsoft’s Power Query documentation explains that locale affects conversion from text into dates. In its editor, Change Type and Using Locale let you choose the date type and the locale used to interpret the text. Select the convention established from the source.

If your Excel edition uses the legacy Text Import Wizard, its Date option allows a specified date order such as MDY or DMY. The wizard is a legacy option, so consult Microsoft’s instructions for enabling or using it rather than assuming it appears in every installation.

Verify the date values with a small test set

Use three checks: a date whose day exceeds twelve, a date where both numbers are twelve or less, and a row near the statement boundary. Compare the parsed year, month and day with the original, not just the visual width or alignment of the cell.

For the fictional April file, verify that 04/05/2026 becomes April 5 and that 04/23/2026 becomes April 23. Then inspect any row outside the printed period. Such a row may reveal a parsing error or a different date field, so investigate before deleting it. A sorted list can look orderly even when every ambiguous date is wrong.

Prepare the destination file without losing the original

Use one date representation that the receiving system documents for the import you are performing. A working review file may use year-month-day for readability, but that choice is not a universal guarantee of importer compatibility.

Keep the raw dates in your review workbook and compare the final exported text with the parsed values. If you save a CSV, reopen it as text for inspection so the spreadsheet does not silently reinterpret it again. The related Excel import guide covers the rest of the columns.