Bank statements

Bank statement dates landing in the wrong month in Excel: how to fix

Fix Excel bank statement dates that land in the wrong month after import: spot day-month swaps, convert text dates with a formula and recheck monthly totals.

Editorial illustration for Bank statement dates landing in the wrong month in Excel: how to fix

The question on the forum: I imported my bank statement into Excel and some transactions are in the wrong month, how do I fix it? Excel is reading day and month in the wrong order. The fix is to convert the date column with the right order, either with Text to Columns or with a formula, and then recheck each month's total.

How the mix-up happens

A statement says 03/04/2026 and means 3 April. A computer set to read month first sees 4 March instead. Dates where the first number is above 12, such as 25/04/2026, cannot be a month, so Excel leaves them as text. That gives the telltale mix: some dates flipped and moved, others sitting left-aligned as plain text.

Spot the damage first

  • Dates left-aligned in the cell are text; real dates align right.
  • Dates that jump to odd months, such as February transactions in a file that should only cover April.
  • A month total that is lower than the statement shows.

Say April's real spending is ₹41,800 and the transactions dated 1 to 12 April add up to ₹15,300. Those are flipped into other months, so your April total shows 41,800 - 15,300 = ₹26,500. The missing ₹15,300 is sitting in January, February, March and the like.

Fix 1: Text to Columns

  1. Select the date column.
  2. Choose Data, then Text to Columns, then Next twice.
  3. In step three choose Date and pick DMY from the list.
  4. Finish. Excel re-reads the column with day first.

Run it on the original text version of the column. If Excel has already flipped some dates, undo or re-import first, since the flipped ones are already wrong values.

Fix 2: a formula for text dates

If the dates are text in dd/mm/yyyy form in A2, this builds a real date from the pieces:

=DATE(VALUE(RIGHT(A2,4)),VALUE(MID(A2,4,2)),VALUE(LEFT(A2,2)))

Fill it down, format the result as a date, then copy and paste as values. It works whatever your regional settings are, as long as the text really is two digits, two digits and four digits. If your file uses another separator or a 2-digit year, adjust the positions.

If some dates were already flipped into real dates while others stayed as text, one formula can handle both kinds. For a mixed column in A2:

=IF(ISNUMBER(A2),DATE(YEAR(A2),DAY(A2),MONTH(A2)),DATE(VALUE(RIGHT(A2,4)),VALUE(MID(A2,4,2)),VALUE(LEFT(A2,2))))

The first branch swaps day and month on dates Excel flipped; the second builds a date from text. Test it on three rows where you know the answer before filling the whole column, and only use it when every real date in the column is a flipped one.

Fix 3: set the regional order before opening

In Google Sheets, File, Settings lets you choose a locale such as India, and imported dates follow it. In Excel, the order comes from your Windows or Mac region setting. Changing it is a system setting, so only do it if you know what else it affects. The formula route is safer.

What does SpendixAI do?

SpendixAI reads Excel files that include dates with a time, and Excel files written with namespace-prefixed internals, which some exports produce. You still review the rows before saving, so check the first and last date against the statement period. If you work by hand, the checklist above applies.

A short routine for next time

  • Look at the first and last dates after every import.
  • Check that no date falls outside the statement period.
  • Keep the raw file, so you can redo the conversion.
SPENDIXAI JOURNAL

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

Explore your reports