Data Import & Export

How to Fix Incorrect Date Formatting When Importing CSV Files into Excel

Maira Mehtab Sep 28, 2026 870 views

Question details

The user needs to prevent Excel from reversing day and month values (e.g., UK to US format) when importing CSV files.

Product: Microsoft Excel and Power Query
Device & OS: not provided
Scenario: Importing UK CSV files containing dd/mm/yyyy dates into Excel or Power Query.
Observed behavior: Excel automatically reverses day/month dates into a month/day format based on regional computer settings, leading to inaccurate data interpretation.

Before you start

Before importing your data into Excel, open the original CSV file in a plain text editor like Notepad to verify the exact date format being used by the source system.

Solution 1Recommended

Convert Source Dates to an Unambiguous ISO Format

Changing the dates to the ISO 8601 standard (YYYY-MM-DD) ensures that Excel interprets the day, month, and year correctly regardless of system locale.

The most reliable way to prevent date formatting errors during a CSV import is to pre-format your dates using an internationally recognized standard. The ISO format completely removes ambiguity, stopping Excel from confusing the day and month.

Format Source Data

Open your source application or use a text manipulation tool to format the dates in your CSV to the YYYY-MM-DD format (e.g., 2024-12-31).

Save the CSV

Save the newly formatted data as a standard CSV file to your local drive.

Import into Excel

Open Excel, go to the 'Data' tab, click 'From Text/CSV', and import your file. Excel will universally recognize the ISO format without altering the day and month.

Verify Data Accuracy

Review previously ambiguous dates (such as 01/02/24) in your spreadsheet to ensure they have been parsed exactly as intended.

Tip: If you are generating the CSV from a database or external software, configuring the export settings to output ISO dates will permanently prevent this issue for future imports.

Solution 2

Adjust Locale Settings Using Power Query

Use Power Query's locale settings to explicitly tell Excel the regional origin of the CSV dates, preventing it from applying your computer's default settings.

Smart CSV Handling

Precisely Control Date Formats During CSV Imports with WPS Spreadsheet

WPS Spreadsheet features a highly intuitive Text Import Wizard that gives you granular control over column data types before the data hits your worksheet. You can easily define specific date formats, completely preventing day/month reversals.

1. Open Data Import: Launch WPS Spreadsheet, navigate to the 'Data' tab on the top ribbon, and click 'Import Data'.
2. Select CSV File: Choose 'Import Data' from a local file, select your problematic CSV, and proceed to the Text Import Wizard.
3. Configure Delimiters: Select 'Delimited' in step 1, and choose 'Comma' as your delimiter in step 2 so your data splits into the correct columns.
4. Specify Date Format: In step 3, click on the column containing your dates. Under 'Column data format', select 'Date' and pick the exact source format (e.g., 'DMY' for UK dates).
5. Complete Import: Click 'Finish'. WPS Spreadsheet will instantly load the CSV data, interpreting the dates perfectly according to the format you defined.

Advanced Text-to-Columns wizard for flawless date interpretation.100% compatibility with Microsoft Excel (.xlsx, .csv, .xls) formats.Lightweight, fast, and completely free alternative for processing large datasets.Familiar user interface requiring zero learning curve for existing Excel users.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Excel automatically change my dates from dd/mm to mm/dd?

Excel parses imported data using your computer system's default regional settings. If your computer is set to US standards (mm/dd/yyyy) and the CSV contains UK standard dates (dd/mm/yyyy), Excel tries to match the incoming data to the system setting, which mistakenly causes valid days to be read as months.

What is an ISO date format and why should I use it for CSVs?

The ISO 8601 date format structures dates as Year-Month-Day (YYYY-MM-DD). It is universally recognized as unambiguous. Using this format ensures that any spreadsheet application will consistently process the day and month correctly, regardless of regional settings.

Can I fix reversed CSV dates after they are already imported into Excel?

Yes. If the dates are already reversed in your worksheet, highlight the affected column and navigate to Data > Text to Columns. Choose 'Delimited', click next until Step 3, choose 'Date', and select the format that reflects how the data currently appears (like MDY). Excel will convert them back into true date serial numbers.

Excel & Data Problems