Spreadsheets

Excel not recognising dd/mm/yyyy dates from a bank CSV

Excel treats dd/mm/yyyy dates from a bank CSV as text or swaps the day and month. See why, and fix it with Text to Columns or a DATE formula.

Editorial illustration for Excel not recognising dd/mm/yyyy dates from a bank CSV

When Excel does not recognise dd/mm/yyyy dates from a bank CSV, it is because your computer's regional date order is set to month-first, so a value such as 27/03/2025 is not a valid month-first date and stays as text. Worse, 05/03/2025 may be read as 3 May with no warning. Fix it by converting the column with Text to Columns using the DMY option, or by rebuilding each date with the DATE function.

How can I tell whether the dates are text?

Look at the alignment first. Real dates align to the right of the cell by default; text aligns left. Then test one cell with =ISNUMBER(A2): TRUE means a real date, FALSE means text. Also scan for a date such as 05/03/2025 that Excel has accepted: if your statement says 5 March but Excel shows 3 May, the day and month have been swapped. The swap only happens when both parts could be a month, so a column can be a mix of correct-looking dates, swapped dates and left-aligned text.

  • Left-aligned dates with no change when you apply a date format: text.
  • Days 13 to 31 stay as text; days 1 to 12 may become wrong dates.
  • A leading apostrophe or a stray space before the date also forces text.

What is the quickest fix with Text to Columns?

This works when the dates are still text. Keep a copy of the file first.

  1. Select the date column only.
  2. Go to Data, then Text to Columns.
  3. Choose Delimited, press Next, untick every delimiter, press Next again.
  4. Under Column data format choose Date and pick DMY from the list beside it.
  5. Press Finish, then format the column as dd/mm/yyyy.

Excel now reads each value as day, month, year regardless of your system setting. If the swap has already happened (the dates were accepted as month-first), Text to Columns will not help, because they are real dates now. Close the file without saving and reopen the CSV through the data import route (Data, then From Text/CSV) so you can set the column type before the dates are parsed, or use the formula below on the text version.

What is the formula that rebuilds the date?

If every date is exactly ten characters like 05/03/2025, put this in a spare column:

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

RIGHT takes the year, MID takes the two characters starting at position 4 as the month, and LEFT takes the day. DATE assembles them in the order year, month, day, and Excel does not care about your regional setting. If dates can be written as 5/3/2025 with no leading zeros, use the version that locates the slashes:

=DATE(VALUE(RIGHT(A2,4)),VALUE(MID(A2,FIND("/",A2)+1,FIND("/",A2,FIND("/",A2)+1)-FIND("/",A2)-1)),VALUE(LEFT(A2,FIND("/",A2)-1)))

The middle part finds the first slash, then the second slash, and takes whatever lies between them as the month. If stray spaces are present, wrap the cell in TRIM, as in TRIM(A2). Once the new column looks right, copy it, Paste Special as values over the original, and delete the helper.

Text in the fileMonth-first setting reads it asAfter DATE rebuild
27/03/2025Text, cannot be a date27 March 2025
05/03/20253 May 2025 (wrong)5 March 2025
12/11/202511 December 2025 (wrong)12 November 2025

Why does this keep happening?

The CSV has no concept of date format; it is just characters. Excel guesses using the Windows or Mac regional setting at the moment of opening. A bank that writes dd/mm/yyyy meets a computer expecting mm/dd/yyyy, and the guess goes wrong. You can change the region format in your operating system settings to English (India) so double-clicking a CSV parses dates as day-first, but that changes every file you open, so check with a known date afterwards.

Before you do anything else

  • Save the original CSV unchanged.
  • Convert in a helper column so you can compare against the original.
  • Spot-check at least one date with a day above 12 and one below.
  • Paste values, then delete the helper column.

SpendixAI accepts bank statements as PDF, CSV or Excel and shows each row for review before saving, which lets you catch a wrong date before it lands in your records, though you should still check dates against your statement.

SPENDIXAI JOURNAL

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

Explore your reports