How to Fix Mixed DMY and MDY Date Formats in Excel CSV Files
Question details
Users need a way to correctly import and parse CSV data into Excel when the file contains inconsistent date formats, specifically a mix of day-month-year and month-day-year.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing a CSV file into an Excel workbook where the raw data contains dates formatted with varying regional standards.
- Observed behavior
- Excel automatically misinterprets ambiguous dates based on system settings, confusing months and days (e.g., interpreting 01-08-2021 as August 1 instead of January 8).
Open your raw CSV file in a plain text editor like Notepad first to inspect the data and confirm the intended meaning of ambiguous values (e.g., determining if 01-08-2021 means August 1 or January 8 based on context).
Standardize Dates Using Power Query and Locale Settings
Use Excel's Power Query Editor to import the CSV and define a specific regional locale for the date column, which prevents Excel from guessing and corrupting the formats.
Power Query allows you to bypass Excel's default automatic formatting behavior. By defining the locale during the column type transformation, you can force Excel to read the raw text strictly as Day-Month-Year or Month-Day-Year according to your explicit instruction.
Open Excel, navigate to the 'Data' tab on the ribbon, and select 'From Text/CSV' to locate and open your file.
In the preview window that appears, do not click 'Load'. Instead, click 'Transform Data' (or 'Edit' in older versions) to open the Power Query Editor.
Locate the column containing your dates. Right-click the column header, hover over 'Change Type', and select 'Using Locale...' from the context menu.
In the Change Type with Locale dialog box, set the Data Type to 'Date'. Then, select the Locale that matches the majority of your data (e.g., 'English (United Kingdom)' for DMY or 'English (United States)' for MDY). Click OK.
Verify that ambiguous dates are now parsed correctly in the preview window. Finally, click 'Close & Load' in the top-left corner to import the standardized data into your spreadsheet.
Use Conditional Custom Columns in Power Query
If the source data contains genuinely mixed formats (e.g., some dates use slashes for MDY and dashes for DMY), use conditional logic to parse them separately before converting them into dates.
Import and Standardize CSV Dates in WPS Office
WPS Spreadsheet features a highly intuitive Text Import Wizard that allows you to manually specify date column formats (such as YMD, DMY, or MDY) during the import process, completely sidestepping unwanted automatic data conversions.
- 1. Launch the Data Import Wizard: Open WPS Spreadsheet, navigate to the 'Data' tab, and click on 'Import Data'. Select your problematic CSV file.
- 2. Choose the Delimited Option: In the Text Import Wizard, select 'Delimited' and proceed to the next step to set your commas or tabs as separators.
- 3. Highlight the Date Column: In Step 3 of the wizard, click on the column containing your dates in the Data preview section so it is highlighted.
- 4. Set the Date Format: Under 'Column data format' in the top right corner, select the 'Date' radio button. Click the dropdown menu next to it and select the exact format your raw data uses (e.g., DMY or MDY).
- 5. Finish the Import: Click 'Finish'. WPS Spreadsheet will instantly import the data, interpreting every date correctly based on the format you explicitly defined.
Frequently Asked Questions
Why does Excel change 01-08-2021 to August 1 instead of January 8?
Excel relies on your computer's system regional settings to interpret dates upon import. If your system is configured to a US locale (Month-Day-Year) but the CSV data was generated in a UK or India locale (Day-Month-Year), Excel will incorrectly read the first digit '01' as the month instead of the day.
How do I access the Power Query Editor if the Transform option is missing?
If you are using an older version of Excel and don't see 'Transform Data', go to the Data tab, select 'New Query' or 'Get Data', choose 'From File', and then 'From Text/CSV'. The button to enter Power Query may be labeled 'Edit' instead of 'Transform Data'.
Can I fix mixed CSV date formats using standard Excel formulas instead?
Yes. You can import the CSV entirely as Text to prevent Excel from altering values, and then use formulas combining DATE, MID, LEFT, and RIGHT along with IF statements to parse the text. However, this method is generally much more complex and time-consuming than using Power Query.
What if my CSV file uses both slashes and dashes for different date formats?
You can use the differing separators to your advantage. In Power Query, you can split the column by delimiter or write a conditional column statement that applies one formatting rule if the string contains a slash (/), and a different formatting rule if it contains a dash (-).
