The International Date Problem in Bank Statements
When importing statements from international banks into Microsoft Excel, dates frequently scramble or split into two incompatible data types within the same column. For instance, a transaction recorded on 04/05/2026 in the UK (May 4th) opens on a computer with US regional settings as April 5th.
Even more hazardous is "silent date swapping". When a CSV is opened in Excel with regional settings that expect month-first dates, a day-first value such as 03/08/2026 (3 August) is read as March 8 without any warning. When the day is greater than 12 (e.g., 25/08/2026), Excel cannot read "25" as a month, so it leaves that cell as text. The result is a mixed column: some cells are (possibly wrong) date serial numbers and the rest are text, so sorting and date filters misbehave.
Do not assume one date format for all Canadian statements: there is no uniform standard. Depending on the bank and the product, you may see numeric dates, month names, or (on French-language statements) French month abbreviations. Look at a date with a day greater than 12 on your own statement before applying a rule to the whole column.
Method 1: The "Text to Columns" Date Fix (No Formulas Required)
The fastest built-in way to force Excel to re-parse an entire column of international dates is the Text to Columns wizard:
1. Highlight the entire column of problematic dates.
2. Navigate to the Data tab on the top ribbon and click "Text to Columns".
3. Select "Delimited" on Step 1 and click Next.
4. On Step 2, uncheck all delimiter boxes (Tab, Comma, Space) and click Next.
5. On Step 3, under "Column data format", select "Date". In the adjacent dropdown, pick the order in which the dates are written in your statement: "DMY" for day-first dates (common in the UK and Australia), "MDY" for month-first dates (common in the US), or "YMD" for year-first dates such as 2026-10-15.
6. Click Finish.
Excel converts the text dates in the column into real Excel dates using the order you chose.
Text to Columns only re-reads dates that are still stored as text. If Excel already turned 03/08/2026 into March 8, that cell is now a number and nothing in it records that the day and month were swapped, so no formula can tell a swapped date from a correct one. The reliable fix is to go back to the original file and import it again with the date column set to Text or to the correct day/month order (Data > From Text/CSV). Only swap day and month with a formula if you know every converted value in that column was misread.
Method 2: Formula-Based Date Extraction Using DATE, MID, and LEFT
If your dates are still text in the exact form DD/MM/YYYY with two-digit day and month (e.g., "18/09/2026" in cell A2), use the DATE function to assemble the day, month, and year explicitly. The positions in this formula assume that exact layout; adjust them for other layouts:
=DATE(RIGHT(A2, 4), MID(A2, 4, 2), LEFT(A2, 2))How this formula works:
• RIGHT(A2, 4) extracts the 4-digit year ("2026").
• MID(A2, 4, 2) extracts the 2-digit month ("09").
• LEFT(A2, 2) extracts the 2-digit day ("18").
• DATE(year, month, day) constructs a valid serial date that formats cleanly according to your Excel settings.
Handling Canadian and Alphanumeric Statement Dates
Because Canadian and some international institutions use alphanumeric dates (such as "15-OCT-2026" or "OCT 15 2026"), standard numeric date slicing will fail if month character lengths vary.
For alphanumeric dates, use Excel's DATEVALUE function combined with TRIM:
=DATEVALUE(TRIM(A2))DATEVALUE reads text using your computer's date settings. With English settings it generally recognizes three-letter English month names, as in "15-OCT-2026"; test a few cells first, because other layouts can return #VALUE!. For French month abbreviations (e.g. "15-AVR-2026"), replace the month with its English abbreviation first:
=DATEVALUE(SUBSTITUTE(A2, "AVR", "APR"))Automated Conversion Alternative
PDFtoRow reads dates from the statement PDF itself, so there is no CSV re-interpretation step. In the Excel file, a date becomes a real Excel date only when it includes a year, the day/month order can be determined from the statement, and it displays exactly as printed; otherwise the original text is kept rather than guessed. The CSV file contains the dates exactly as printed, so if you open that CSV in Excel, import it as described above.
Convert Bank Statement PDFs Cleanly with PDFtoRow
Tired of fixing broken columns, corrupted dates, and numbers stored as text? PDFtoRow extracts transaction tables directly from digital PDF vector layers, preserving check numbers and outputting true numeric amounts. Statements up to 5 pages are free once per email every 24 hours. Check the 10-row preview before you download.
Frequently Asked Questions
Excel stores all dates internally as sequential serial numbers representing days elapsed since January 1, 1900. A number like 46320 simply means the cell is formatted as "General" or "Number". Change the cell format to "Short Date" via the Home tab to display it as a calendar date.
Changing your regional settings changes how Excel reads dates the next time a file is opened or imported, but it does not repair dates that were already swapped or left as text. Re-import the original file, or use Text to Columns or the DATE formula on dates that are still text.
In an empty cell, enter =ISNUMBER(A2). FALSE means the cell is text. TRUE only means the cell holds a number (Excel stores dates as numbers); it does not prove the day and month are correct. To check correctness, display the column in an unambiguous format such as dd mmm yyyy and compare several rows, especially ones with a day of 12 or less, against the original statement.
Primary Evidence & Official References
- DATE function — Microsoft Support (Verified 2026-10-15). Official source documentation →