Guide · StatementTidy
How to convert DD/MM/YYYY dates in Excel without day and month swapping
Short answer
If your Excel uses US settings, 05/04/2026 becomes 4 May and 25/04/2026 stays text, leaving a column of wrong dates mixed with text. Convert while the dates are still text: Text to Columns → Date: DMY, or =DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2)). StatementTidy decides DD/MM vs MM/DD from the whole column and outputs ISO YYYY-MM-DD, which no spreadsheet misreads.
Last updated · by Mumshad Jawad
Why this happens
A date like 05/04/2026 is ambiguous: 5 April in the UK, UAE, India and most of the world, 4 May in the US. Excel resolves it with your computer’s regional settings, row by row, so a DD/MM statement opened on a MM/DD machine ends up with:
- days 1–12 silently converted to the wrong date (month and day swapped), and
- days 13–31 left as text, because there is no month 13.
Sorting and monthly pivots then look plausible and are wrong. The safest fix is to never let Excel guess: give it unambiguous ISO dates.
Real example: before and after
The rows below are fictional. The “after” table is not a mock-up: this page ran them through StatementTidy’s cleaning engine (the same clean.js the extension uses, v1.0.1) with its default options, and shows exactly what you would export.
| Value date | Narrative | Amount |
|---|---|---|
| 01/04/2026 | OPENING BALANCE | 2,000.00 |
| 05/04/2026 | TALABAT ORDER 88172 | -64.50 |
| 09/04/2026 | SALIK TOPUP | -100.00 |
| 12/04/2026 | IPP TRANSFER FROM A KHAN | 2,500.00 |
| 13/04/2026 | NETFLIX.COM | -56.00 |
| 25/04/2026 | SALARY APR 2026 | 18,750.00 |
| Value date | Narrative | Amount | Row flag |
|---|---|---|---|
| 2026-04-01 | OPENING BALANCE | 2000.00 | balance |
| 2026-04-05 | TALABAT ORDER 88172 | -64.50 | |
| 2026-04-09 | SALIK TOPUP | -100.00 | |
| 2026-04-12 | IPP TRANSFER FROM A KHAN | 2500.00 | |
| 2026-04-13 | NETFLIX.COM | -56.00 | |
| 2026-04-25 | SALARY APR 2026 | 18750.00 |
- Date column converted to ISO YYYY-MM-DD (DD/MM detected)
- 3 negative amounts as real negative numbers
- 1 total/balance row flagged, left out of sums
Open this example in the live demoEdit the rows, paste your own table, toggle options. Nothing is uploaded.
How to fix it by hand in Excel
While the dates are still text
Select the column, Data → Text to Columns → Delimited → Next → Next, set Column data format: Date and choose DMY, then Finish.
Or with a formula, for text in exactly dd/mm/yyyy form:
=DATE(RIGHT(A2,4), MID(A2,4,2), LEFT(A2,2))
Then Format Cells → Custom → yyyy-mm-dd.
When Excel has already converted some of them
The converted ones are now real dates with day and month swapped, so the formula above no longer applies to them. Re-import instead: Data → From Text/CSV, click Transform Data, right-click the date column → Change Type → Using Locale… → Date, English (United Kingdom).
Google Sheets
File → Settings → Locale controls how pasted dates are read. Set it to the statement’s country before pasting.
How to fix it in one click with StatementTidy
- Install StatementTidy. Download the free extension and load it in Chrome, Edge or Brave.
- Open the statement. Any page with a DD/MM or MM/DD date column, including 5-Mar-26 and month names in seven languages.
- Click the StatementTidy icon. The chip shows which order was detected. To override, set Dates to DD/MM/YYYY or MM/DD/YYYY in the popup.
- Copy for Excel. Dates arrive as YYYY-MM-DD and Excel stores them as real dates whatever your regional settings.
Get StatementTidy free for ChromeTry it on a sample statement
Questions
How does StatementTidy know the order?
It reads every date in the column. A date whose first number is above 12 (25/04/2026) can only be DD/MM; one whose second number is above 12 (04/25/2026) can only be MM/DD. The order with more such dates wins. If every date is ambiguous it uses the page’s language: US English means MM/DD, anything else DD/MM.
Why ISO dates?
YYYY-MM-DD has only one reading, sorts correctly as text, and is recognised as a date by Excel, Google Sheets, Numbers and accounting importers in every locale.
Why was the OPENING BALANCE row flagged?
Balance-forward rows are not transactions. StatementTidy marks them in a Row flag column and leaves them out of the column summary so they never inflate a total.