Data Import & Export

How to Correct US and UK Date Interpretation in Excel

Adam DavisAdam Davis Nov 11, 2026 868 views

Question details

Users need to correct dates imported in the US format (MM/DD/YYYY) that Excel mistakenly interprets as the UK format (DD/MM/YYYY).

How to Correct US and UK Date Interpretation in Excel
Product
Excel
Device & OS
not provided
Scenario
Importing CSV or text data containing dates where the system locale and the document data format do not match.
Observed behavior
Excel incorrectly interprets the month and day for ambiguous dates (e.g., treating Jan 5th as May 1st). Changing the display format does not resolve the issue because the underlying date serial number is already stored incorrectly.
Before you start

Widen your date column and remove any alignment formatting to check if the dates are right-aligned (stored as actual numeric date values) or left-aligned (stored as plain text strings).

Solution 1Recommended

Re-import the Data Specifying the Correct Locale

The most reliable method to ensure dates are imported correctly is to specify the source data's locale during the initial import process.

When importing a CSV file, Excel tries to guess the date format based on your computer's regional settings. If your computer is set to UK but the file uses US dates, ambiguous dates like 01/05/2024 will be interpreted wrong. Using the Get Data feature allows you to override this.

1
Start the import process

Open a blank workbook, navigate to the Data tab, and click on 'From Text/CSV'.

2
Select your file

Locate and select the CSV or text file containing your dates, then click 'Import'.

3
Adjust the Data Type locale

In the preview window, instead of clicking Load immediately, click 'Transform Data'. Right-click the column header for your dates, choose 'Change Type', then select 'Using Locale'.

4
Set locale to US

In the dialog box, set the Data Type to 'Date' and the Locale to 'English (United States)'. Click OK, then click 'Close & Load' to import the correctly interpreted dates.

Re-import the Data Specifying the Correct Locale
Best Practice: This method prevents the issue entirely, ensuring that both ambiguous dates (like 01/05) and obvious text dates (like 12/25) are converted into true serial date numbers.
Effortless Data Management in WPS Office

Fix Date Formats Easily in WPS Spreadsheet

WPS Spreadsheet offers powerful, intuitive tools like Text to Columns and precise Data Import wizards to accurately handle complex US and UK date formats, ensuring your reports are error-free.

  1. 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the CSV or spreadsheet file containing the problematic dates.
  2. 2. Select the dates: Highlight the entire column where the US/UK date misinterpretation has occurred.
  3. 3. Launch Text to Columns: Navigate to the Data tab on the top ribbon and select the 'Text to Columns' tool.
  4. 4. Convert the format: Skip to the third step of the conversion wizard. Choose 'Date', select 'MDY' to match the original source format, and click 'Finish' to instantly correct the data.
Fully compatible with Microsoft Excel (.xlsx) and CSV formatsIntuitive Text to Columns wizard to easily fix US and UK date importsLightweight application that quickly processes large datasetsFamiliar user interface requiring zero learning curve
microsoft office alternative - wps office

Frequently Asked Questions

Why does changing the cell format from US to UK not fix the date?

Changing the number format only alters how an underlying serial number is displayed. If Excel initially interpreted January 5 (01/05) as May 1, the underlying number saved is for May 1. You cannot format your way out of bad data; you must re-import or rebuild the date values.

How can I tell if my dates are stored as text or actual numbers?

Expand the width of the column and clear any custom alignment formatting. By default, Excel aligns text strings to the left and actual numbers (including valid dates) to the right.

What happens to dates like 12/25/2024 when opened on a UK system?

Because a UK system expects the day first and there is no 25th month, it fails to recognize 12/25/2024 as a valid date. Instead of converting it to a date serial number, it will leave it stored as a plain text string.

Does the 'Text to Columns' method work for all incorrect dates?

The Text to Columns method works perfectly if the dates are currently stored as text. If Excel has already converted the ambiguous dates into incorrect numerical date values, Text to Columns will not work, and you will need to use the DATE formula method to swap the months and days.