DD/MM vs MM/DD: fixing date formats in bank statement spreadsheets
Day-month swaps are the most dangerous kind of data error because every swapped date still looks like a valid date. Here is how they happen, how to detect them, and how to repair them without guessing.
By Muhammad Sharif · October 2026 · 8 min read
Take the date 05/04/2026. In the United States it means 4 May. In India, the United Kingdom, Australia and most of Europe it means 5 April. Nothing in the text tells you which. When a statement from one country is read by software configured for another, every ambiguous date quietly becomes a different, perfectly valid date, and nothing flags it. For bank data that means transactions in the wrong month, wrong period totals and failed reconciliations that are hard to trace.
Why this is only a problem sometimes
A swap can only be ambiguous when both numbers are 12 or less. A date such as 25/03/2026 can only be day-first, since there is no month 25, and 03/25/2026 can only be month-first. So most statements contain some dates that settle the question, and some that do not. Roughly, the first twelve days of every month are ambiguous, and the 13th to 31st are not.
That asymmetry is useful. It means a whole statement can usually be decoded by finding a single unambiguous date. If any date has a first number above 12, the statement is day-first; if any has a second number above 12, it is month-first. Converters can use the same logic, and the converter on this site does: it scans the statement for such dates and picks the order that matches, falling back to a hint (for example, Indian bank pages assume day-first) only when every date is ambiguous.
How to detect a swap in your data
- Check the statement period. The header says, for example, "1 March to 31 March 2026". After conversion, every date should fall inside that range. A February or April date in a March statement is a swap.
- Sort by date. Original statements run in order. If sorting by the converted date shuffles rows into an odd pattern, with dates jumping between months, you have swapped values.
- Find a date past the 12th. Locate a transaction on, say, the 27th in the PDF and confirm your sheet shows 27 March rather than failing to parse. In Excel, a text cell left-aligned in a date column is often a day-first value that Excel could not read as month-first.
- Test with a formula. In a helper column,
=AND(A2>=DATE(2026,3,1),A2<=DATE(2026,3,31))returns FALSE for any date outside the statement period, and you can filter for FALSE.
How Excel and Sheets decide
When you open a CSV or paste text, the program converts anything that looks like a date using your computer's regional settings (Windows Region, macOS Language & Region, or the Google Sheets locale under File → Settings). Where those settings do not match the file, three things can happen:
- An unambiguous date that fits your setting converts correctly.
- An ambiguous date converts, but swapped.
- A date that does not fit your setting, such as 25/03/2026 on a month-first computer, is left as text.
That last case explains why some rows turn into dates and others stay text, and why the mixed column looks fine at first glance.
The safest fix: use an unambiguous format from the start
ISO 8601 order, year-month-day as in 2026-03-05, cannot be misread. It reads the same everywhere, sorts correctly as text, and is understood by Excel, Sheets, QuickBooks and Xero's importers. Where you control the export, use it. The CSV from this site uses it for that reason. If your accounting package requires a specific regional style on import, convert from ISO at the last step.
Converting existing dates safely
When the column is text in a known order
If the column holds text such as 05/04/2026 that you know is day-first, rebuild real dates with formulas that do not depend on regional settings. Assuming the text is in A2:
=DATE(VALUE(RIGHT(A2,4)),VALUE(MID(A2,4,2)),VALUE(LEFT(A2,2)))
For month-first text, swap the middle and left parts:
=DATE(VALUE(RIGHT(A2,4)),VALUE(LEFT(A2,2)),VALUE(MID(A2,4,2)))
Then format the result cells as Date, copy, and Paste Special → Values over the original.
When Excel already converted it wrongly
Do not try to fix dates that have already been reinterpreted by swapping them back selectively, because you cannot tell which rows were changed. Go back to the source file and re-import with the column set to Text (Data → From Text/CSV → Transform, or the import wizard's column data format), then apply the formulas above.
When you only need to change the display
If the dates are real and correct, only displayed in the wrong style, select the column, open Format Cells → Date (or Custom) and pick the order you want, for example dd-mmm-yyyy. Writing the month as letters, as in 05-Apr-2026, removes any ambiguity for human readers.
Two-digit years and other traps
- Two-digit years. 05/04/26 is probably 2026, but spreadsheet programs use a pivot rule (usually 1930 to 2029) that you should not rely on across decades. Use four digits when you can.
- Dates without a year. Some credit card statements print only 03/14. Use the statement period to supply the year, and watch for statements that straddle a year end, where December rows belong to the earlier year.
- Value date versus transaction date. Many Indian and European statements show two dates per row. Decide which one you are reconciling to, and keep it consistent.
- Time zones and timestamps. Some CSVs include a time and a zone, and converting between systems can shift a late-evening transaction into the next day.
Frequently asked questions
Can software always tell which order a statement uses?
No. If every date in the file has both numbers at 12 or below, it is genuinely ambiguous. Good tools then fall back to a regional hint, and you should verify the first and last dates against the statement period.
Which order should I use for my accounting software?
The one your software's region setting expects, because that is how it will interpret the file. Check your accounting software's help for the import rules rather than assuming.
Why do some of my dates align left and others right?
Real dates are numbers and align right by default; text aligns left. A mixed column means Excel converted some values and left others as text, usually because of an order mismatch.
