How to open a CSV in Excel without breaking the dates

Why it happens

When you double-click a CSV, Excel reads dates using your computer’s regional settings. With UK settings, dd/mm/yyyy is read correctly. With US settings, Excel reads the first number as the month: 03/04/2026 becomes 4 March, and 25/04/2026, which can’t be a US date, is left as text. Half your dates are wrong and the rest aren’t dates at all.

The quick fix, if it’s your own computer, is to set the regional format to United Kingdom in Windows or macOS settings. If you can’t change it, import the file instead of opening it.

Step by step

1. Start from a blank workbookOpen Excel with a new, empty workbook. Don’t double-click the CSV.
2. Import the CSVGo to Data, From Text/CSV, and choose the file. A preview appears.
3. Choose Transform DataInstead of Load, click Transform Data to open Power Query.
4. Set the date column with a localeRight-click the Date column heading, choose Change Type, Using Locale. Pick Date and English (United Kingdom). Click OK.
5. Load itChoose Close & Load. The dates are now real dates, the right way round.
6. Save as a workbookSave as .xlsx. If you later save back to CSV on a US-set computer, Excel may write mm/dd/yyyy, so keep the original CSV for importing elsewhere.

The older Text Import Wizard

If you prefer the classic wizard, enable it under File, Options, Data (“Show legacy data import wizards”), then use Data, Get Data, Legacy Wizards, From Text. In step 3, select the Date column and choose Date: DMY.

Don’t let Excel “fix” the CSV before importing elsewhere

If you’re importing into Xero, QuickBooks or FreeAgent, upload the CSV exactly as downloaded. Opening and re-saving it in Excel can silently change dates and remove leading zeros.