Why this happens
A date written as 03/04/2024 has no single meaning. In the UK, Ireland, most of Europe, Australia and much of the rest of the world it is 3 April. In the US it is 4 March. A CSV stores it as plain text, with nothing to say which convention the bank used.
When Excel opens a CSV, it reads dates using the regional settings of your computer, not of the file. On a computer set to US English, it reads 03/04/2024 as 4 March. Then it reaches 15/04/2024, which can’t be month 15, so it gives up and keeps that cell as text. The result is a column in which every date that could be read either way has been converted wrongly and silently, and the rest are text. Sorting, filtering and date arithmetic give nonsense, and an accounting import either rejects the text dates or books the converted ones to the wrong month.
The reverse happens when a day-first computer opens a US export. And once Excel has converted the wrong dates, they are real date values, so saving the file locks the mistake in: the original text is gone and the saved CSV contains the swapped dates. That is why every fix below starts from the original export, not from a copy that has been opened and saved.
Mixed formats add a second layer. Files merged from two sources, or exports where someone has edited a few rows by hand, can hold 2024-04-16, 16 Apr 2024 and 16/04/2024 in the same column. An import screen asks you for one date format and applies it to every row, so a single stray format is enough to fail the import or move a payment into a different period.
The same guessing reaches beyond date columns. Product and part codes such as 1-10 or MAR1 are turned into dates, IDs like 00123 lose their leading zeros, and account or card numbers longer than 15 digits are shown in scientific notation with the trailing digits replaced by zeros. None of these can be reversed after the file has been saved, which is another reason to fix the file before Excel opens it.
Fix it by hand
In Excel, with Power Query
- 1
Start from the original CSV, not a copy that was opened and saved in Excel.
- 2
In a blank workbook, go to Data > From Text/CSV, pick the file and click Transform Data.
- 3
Right-click the date column and choose Change Type > Using Locale. Set the data type to Date and the locale to one that writes dates the way your bank does, for example English (United Kingdom) for day-first dates.
- 4
Click Close & Load. Every date in the column is read with the order you chose, whatever your computer’s settings are.
In Excel, with Text to Columns
- 1
Select the date column and choose Data > Text to Columns.
- 2
Click Next twice, choose Date in step 3, and pick the order the file uses, such as DMY.
- 3
Click Finish. Excel converts each text date using that order.
This only helps with cells that are still text. Dates Excel has already converted the wrong way are numbers now, and have to come from the original file.
In Google Sheets
- 1
Before importing, open File > Settings and set Locale to a country that uses the file’s date order, such as United Kingdom.
- 2
Import with File > Import and leave the option to convert text to numbers, dates and formulas switched on.
- 3
Compare a few dates whose day is 12 or lower with the bank statement before you rely on the result.
When one column mixes several formats
- 1
Sort or filter the column so that text dates separate from real ones; text is left-aligned by default.
- 2
Convert the leftovers with a formula, for example =DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2)) for text in DD/MM/YYYY form with two-digit days and months.
- 3
Paste the results back as values and give the whole column one format, ideally yyyy-mm-dd.
In Microsoft 365, turn off automatic conversions
- 1
Go to File > Options > Data and find the Automatic Data Conversion section.
- 2
Untick the conversions you don’t want: removing leading zeros, keeping only the first 15 digits of long numbers, converting digits around the letter E to scientific notation, and converting continuous letters and numbers to a date.
- 3
Click OK and reopen the CSV. Codes like MAR1 and IDs like 00123 now stay as they are in the file.
These settings apply to every file that copy of Excel opens, and older versions don’t have them. They don’t change how numeric dates such as 03/04/2024 are read, which still follows your regional settings, so use one of the methods above for those.
Fix it with SpreadsheetFix
SpreadsheetFix reads the dates as text, exactly as the bank wrote them, before any spreadsheet has had a chance to convert them, and decides the order for each column from the evidence in that column. If any date has a first number above 12, such as 15/04/2024, the column must be day-first. If any has a second number above 12, such as 04/15/2024, it must be month-first. Every other date in the column is then read the same way, so 03/04/2024 next to 15/04/2024 becomes 2024-04-03.
Each column is judged on its own, so a transaction date and a value date produced by different systems can follow different conventions in the same file. ISO dates such as 2024-04-16 and English month names such as 16 Apr 2024 or April 16, 2024 are recognised too. Everything is rewritten as YYYY-MM-DD, which sorts correctly even as text and can’t be misread by the next program that opens it. Cells that aren’t dates are left as they are.
Load the CSV straight from your bank, check the converted dates in the preview, and download. Normalize Dates is a free rule for files up to 5,000 rows, and the statement is read in your browser rather than uploaded.
No file to hand?
The same cleaner is on the Normalize Dates page.
Before and after
date,description,amount 03/04/2024,Direct debit Insurance,-61.20 15/04/2024,Salary,2500.00 2024-04-16,Card payment Fuel,-48.75 18 Apr 2024,Transfer to savings,-300.00
date,description,amount 2024-04-03,Direct debit Insurance,-61.20 2024-04-15,Salary,2500.00 2024-04-16,Card payment Fuel,-48.75 2024-04-18,Transfer to savings,-300.00
15/04/2024 proves the column is day-first, so 03/04/2024 is read as 3 April.
What it won’t catch
- If every date in a column has both numbers at 12 or below, as in an export covering only the first twelve days of a month, there is no evidence either way, and SpreadsheetFix reads the column day-first. For a US export, set the rule’s read order to month first before you clean.
- Two-digit years are read the way Excel reads them: 00 to 29 become 2000 to 2029, and 30 to 99 become 1930 to 1999. They follow the same per-column day/month detection as four-digit years, but a date before 1930 needs a four-digit year to come out right.
- A swap that Excel has already saved into the file can’t be undone from that file. Download the export again and clean the fresh copy.
Questions
Why does Excel swap the day and month in my CSV?
Excel reads CSV dates with your computer’s regional settings. If the file was written day-first and your computer expects month-first, or the other way round, every date with a day of 12 or less is converted the wrong way and the rest are left as text.
How do I stop Excel changing dates when I open a CSV?
Don’t double-click the file. Import it with Data > From Text/CSV and set the column’s locale in Power Query, or use Text to Columns with the right date order. Or convert the dates to YYYY-MM-DD first, which Excel reads the same way in every region.
Which date format should I use for an accounting import?
Whichever the import screen asks for, as long as every row follows it. Many import tools have you pick the file’s date format during column mapping and apply that choice to every row, so a column that mixes formats is what causes rejected rows or entries in the wrong month.
Does SpreadsheetFix change my original file?
No. The file is read in your browser and the original stays untouched on your computer. You download a cleaned copy after checking every change in the preview.