Bank statements

Wrong month after import? How to fix swapped day and month dates

Bank statement dates landing in the wrong month in Excel? Learn why day and month swap, how to spot it in a salaried month, and how to convert text dates.

Editorial illustration for Wrong month after import? How to fix swapped day and month dates

If transactions land in the wrong month, Excel has almost certainly read a day-first date as month-first. A debit on 3 April written 03/04/2026 becomes 4 March, and any date with a day above 12 stays as plain text. Fix it by converting the column with Text to Columns using the DMY option, or rebuild the date with a formula.

Why does the month go wrong?

Indian bank statements print dates with the day first. Excel decides how to read a typed or imported date from your computer's regional settings. When those settings expect month first, 03/04/2026 is stored as 4 March, while 25/04/2026 cannot be a month-first date and stays as text. The result is a column with two kinds of entry, real dates and text, and totals that quietly shift between months.

How do you recognise the problem in thirty seconds?

  • Dates with a day of 13 or more sit on the left of the cell, while others sit on the right.
  • A filter by month shows March rows in a statement that starts in April.
  • The statement's first and last dates do not match the sorted column.
  • A debit you remember paying after salary day appears before it.

A worked example for a salary month

Say your salary arrives on 01/04/2026 and your rent of ₹22,000 goes out on 05/04/2026. A month-first reading turns them into 4 January and 4 May, so the rent appears four months after your salary and the April total looks like only the rows with days above 12. Suppose true April spending is ₹46,500, of which ₹17,000 sits on rows with days up to 12. Excel will report April as the ₹29,500 left over, because 46,500 minus 17,000 is 29,500, and push the ₹17,000 into other months.

Printed dateExcel reads it asCorrect month
01/04/20264 January 2026April
05/04/20264 May 2026April
25/04/2026text, not a dateApril

What are the fixes, in order?

  1. Select the date column. Choose Data, then Text to Columns, click Next twice, and at step three pick Date and choose DMY from the list. Finish.
  2. If the entries are text that will not convert, build the date with =DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)). This works for text in the form 05/04/2026.
  3. Fill the formula down, then copy and Paste Special as values over the original column.
  4. Format the column as a date and sort. The first and last date should match the statement.

To confirm, add =MONTH(A2) in a helper column. April rows should all say 4.

How can you prevent it next month?

Open statements as CSV through Data, then From Text or CSV, where you can set the column type to Date and tell Excel the order. If you only use Excel for these files, check the regional setting once and write down what it is. It is also worth keeping the original file untouched and working on a copy.

Where does SpendixAI come in?

SpendixAI takes statements as PDF, CSV or Excel files and shows every parsed row for review before saving, including its date, so you can compare the first and last dates against the statement before you commit. Files with a time attached to each date and some Excel layouts with unusual internals are the sort of thing an importer has to cope with; do not assume every layout will parse perfectly, and use the review screen to confirm.

A quick check before you trust the month

  • The first and last dates match the statement period.
  • Every cell in the date column right-aligns.
  • A month helper column shows only the expected months.
  • Total spending equals the bank's own summary.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports