Power Query Problems

How to Fix Mixed US and European Date Formats in Excel Power Query

Nimra Malik Oct 15, 2026 870 views

Question details

Users experience inconsistent date interpretations in Excel and Power Query when the dataset contains a mix of US (MM/DD/YYYY) and European (DD/MM/YYYY) date formats.

How to Fix Mixed US and European Date Formats in Excel Power Query

Product: Microsoft Excel
Device & OS: not provided
Scenario: Importing and cleaning data containing mixed regional date formats in Power Query.
Observed behavior: Dates are interpreted inconsistently; some rows display incorrectly while others are converted to the wrong underlying date values.

Before you start

Before transforming your data, ensure you identify which rows correspond to which regional date format, or add an indicator column if possible. It is crucial to prevent Power Query from automatically changing data types upon import.

Solution 1Recommended

Import Dates as Text and Parse with Custom Columns

The best approach for mixed formats is to import the date column as text to prevent automatic, incorrect conversions, then apply logic to parse them correctly.

When Power Query automatically detects data types, it applies a single locale to the entire column. By keeping mixed dates as text, you preserve the original formatting and can apply conditional logic to correctly extract the day, month, and year based on the row's specific format.

Remove Automatic Type Conversion

In the Applied Steps pane on the right side of the Power Query Editor, delete the 'Changed Type' step that automatically formatted your date column upon import.

Set Column to Text

Click the data type icon in the header of your date column and select 'Text' to ensure the raw date string is preserved without Excel attempting to format it.

Add a Conditional Column

Go to the Add Column tab and select 'Conditional Column' or 'Custom Column' to define parsing rules. You can use Text functions (like Text.Start, Text.Middle) to extract the day, month, and year parts conditionally based on the format.

Reconstruct and Convert to Date

Combine the extracted parts into a standard format (e.g., YYYY-MM-DD) and then click the data type icon on the final column's header to change its data type to 'Date'.

Import Dates as Text and Parse with Custom Columns

Requires an Indicator: This method works best if you have a separate column indicating the source region or if you can write a formula to distinguish the formats based on string length or character positions.

Solution 2

Apply Locale-Aware Conversion for Grouped Data

If you can split your query or if specific columns have a consistent regional format, use Power Query's locale settings to convert them properly.

Free Microsoft Office alternative

Handle Complex Data Easily with WPS Office

While Power Query in Excel can sometimes complicate mixed date formats, WPS Spreadsheet offers straightforward Text-to-Columns and intuitive CSV import wizards to give you full control over how your dates are interpreted. As a free, lightweight alternative, WPS Office provides a familiar interface with seamless Microsoft Excel format compatibility.

Fully compatible with Microsoft Excel (.xlsx, .xls, .csv) formatsIntuitive Text-to-Columns wizard for easy date parsingLightweight application that runs smoothly on all devicesFree to download and use with a highly familiar user interface

Frequently Asked Questions

Why does Power Query convert some dates correctly and swap days and months for others?

Power Query uses your system's default locale settings to interpret dates. If your system is set to US (MM/DD/YYYY) and it encounters a European date like 13/05/2023, it fails or swaps values because there is no 13th month.

Can I apply multiple locales to a single column in Power Query?

No, Power Query applies a single data type and locale to an entire column. For mixed formats in one column, you must keep the data as text and parse it conditionally before converting it to a Date type.

How do I stop Power Query from automatically changing data types?

You can delete the 'Changed Type' step in the 'Applied Steps' pane manually, or disable automatic type detection globally by going to File > Options and settings > Query Options > Data Load and unchecking 'Automatically detect column types and headers for unstructured sources'.

Excel & Data Problems