Excel date problems
Fix Bank Statement Dates That Excel Will Not Recognise
Reviewed 12 September 2026
Short answer
Short answer
First identify the source format before converting anything. Australian dates normally use day, month, year, so 03/04/2026 should not be allowed to silently become 4 March. Remove hidden spaces, use Power Query with an English (Australia) locale or build a DATE formula from known day, month and year parts. Then format the resulting value as a date and test an unambiguous day above 12.
Changing the display format does not turn text into a date, and a date that looks correct can still have its day and month reversed.
Fix the value, then choose the display
Start with a date such as 17/04/2026. If Excel cannot parse that correctly, the locale or text cleanup is wrong. Do not test only dates where both numbers are 12 or lower.
Some Australian statements omit the year from transaction rows and print it in the statement period instead. A year rollover around December and January must be handled from that context, not filled with the current year.
Step by step
- 01
Preserve the source text
Copy the original date column before transforming it.
- 02
Remove hidden characters
Trim spaces and replace unusual separators or non-breaking spaces.
- 03
Set the Australian locale
In Power Query, change the type using a locale that reads day before month.
- 04
Handle missing years
Use the statement period and chronological order rather than today's year.
- 05
Test and sort
Check an unambiguous date and sort the full period to reveal rollover mistakes.
Checks before you rely on the spreadsheet
- A day above 12 parses correctly.
- December and January rows use the right year.
- The cell stores a date serial rather than text.
- Sorting produces the statement's real chronology.
Questions people ask
Why does Excel swap the day and month?
Excel may interpret the text using a month-first regional setting. Convert with an Australian locale or construct the date from separate parts.
Why does changing the cell format do nothing?
Number formatting changes how a value displays. It cannot convert an unrecognised text string into a real date.
How should I fill a missing transaction year?
Use the statement start and end period, paying special attention to statements that cross from December into January.