Excel reads a date such as 05/01/2024 using your computer's regional order. On a US-style setting that means month first, so 5 January becomes 1 May, while 18/02/2024 cannot be a month and stays as plain text. That mix is the fingerprint of the problem.
Why only some dates change
Excel tries to turn text into a date as the file opens or as you paste. With month-first rules, a first number from 1 to 12 is a valid month, so the cell converts, with day and month wrongly swapped. A first number from 13 to 31 is not a valid month, so Excel gives up and leaves the cell as left-aligned text. The result is a column that looks half-converted:
| What the statement says | What Excel did (US order) | Result |
|---|---|---|
| 03/02/2024 | Read as 2 March 2024 | Real date, but wrong |
| 11/02/2024 | Read as 2 November 2024 | Real date, but wrong |
| 18/02/2024 | No month 18, so left alone | Text, correct digits |
| 27/02/2024 | No month 27, so left alone | Text, correct digits |
The dangerous ones are the first two: they look like proper dates and nothing warns you. Sorting gives the first clue, because they land far from where the statement order puts them.
How do I repair a column that is already damaged?
Assume dates are in column A, and the text ones are in dd/mm/yyyy with leading zeros. In B2, fill down:
- =IF(ISNUMBER(A2),DATE(YEAR(A2),DAY(A2),MONTH(A2)),DATE(VALUE(RIGHT(A2,4)),VALUE(MID(A2,4,2)),VALUE(LEFT(A2,2))))
For a real date, the formula swaps the month and day back. In the example, 2 March 2024 has year 2024, day 2 and month 3, so DATE(2024,2,3) gives 3 February 2024, which is right. For text, it cuts the day, month and year out by position and builds the date yourself.
Can I tell which cells were swapped without the original?
Partly. Text cells with a day above 12 were never converted, so they are safe. Real-date cells could be either, and the only reliable test is order: if the statement runs forward in time and the repaired date jumps backwards, that row was damaged. When in doubt, go back to the source file and reconvert, rather than guess.
A quick detector helps when you no longer have the original. If the statement runs oldest to newest, put =IF(A3<A2,"out of order","") in B3 and fill down. A genuine statement never goes backwards, so a flagged row is a symptom of a swap nearby.
Say the true dates are 02 Jan, 03 Jan, 05 Jan and 08 Jan, and 03/01 was read as 1 March. The column then runs 2 Jan, 1 Mar, 5 Jan, 8 Jan. The flag appears on 5 Jan, the row after the damaged one, so always look one row up as well. Back-to-back swaps flag fewer rows than they should, which is why the sort-and-compare habit still matters.
How do I stop it happening again?
- For CSV files, do not double-click. Use Data, From Text/CSV, and set the date column to text or to a date with an English (India) locale.
- For pasted data, format the destination column as Text before pasting, and convert afterwards with Text to Columns set to DMY.
- Change the Windows regional format to English (India), so Excel itself expects day first. That fixes new pastes but not cells that are already wrong.
- In Google Sheets, check File, Settings, Locale. Sheets reads dates according to that locale.
What should I check afterwards?
- Sort by date and look for rows out of order.
- Use =COUNT($A$2:$A$500) against your row count: if it falls short, some dates are still text.
- Pick three dates with a day above 12 and three below, and compare them with the statement.
Day-and-month mistakes are the sort that survive into a month's totals without anyone noticing, which is why a statement-period check is worth the thirty seconds. SpendixAI reads dates when you import and shows the rows for review before saving, which is another place to catch a swapped one.
Useful ideas start with a clearer picture of your own spending.
Explore your reports

