Bought Pro? Your keys are in your account. Sign in

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.

Before As copied from the web page
Value dateNarrativeAmount
01/04/2026OPENING BALANCE2,000.00
05/04/2026TALABAT ORDER 88172-64.50
09/04/2026SALIK TOPUP-100.00
12/04/2026IPP TRANSFER FROM A KHAN2,500.00
13/04/2026NETFLIX.COM-56.00
25/04/2026SALARY APR 202618,750.00
After Cleaned by StatementTidy
Value dateNarrativeAmountRow flag
2026-04-01OPENING BALANCE2000.00balance
2026-04-05TALABAT ORDER 88172-64.50
2026-04-09SALIK TOPUP-100.00
2026-04-12IPP TRANSFER FROM A KHAN2500.00
2026-04-13NETFLIX.COM-56.00
2026-04-25SALARY APR 202618750.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

  1. Install StatementTidy. Download the free extension and load it in Chrome, Edge or Brave.
  2. Open the statement. Any page with a DD/MM or MM/DD date column, including 5-Mar-26 and month names in seven languages.
  3. 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.
  4. 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.