Power Query Problems

How to Fix Incorrect Regional Dates in Excel Power Query

Guest Writer Sep 25, 2026 869 views

Question details

The user needs to correct how Power Query interprets text-based dates that are being converted incorrectly due to mismatched regional formats.

How to Fix Incorrect Regional Dates in Excel Power Query

Product: Excel Power Query
Device & OS: not provided
Scenario: Importing or processing text-based dates from sources like SharePoint workbooks into Power Query.
Observed behavior: Power Query interprets text dates like 08/07/24 using the wrong regional setting, swapping months and days (e.g., changing August 7 to July 8), which leads to incorrect date calculations.

Before you start

Verify the exact original format of your source dates (e.g., DD/MM/YYYY vs. MM/DD/YYYY) before adjusting the locale settings in Power Query.

Solution 1Recommended

Convert Text to Date Using Specific Locale

The most reliable method to fix date misinterpretations is to manually convert the text column to a Date type using the exact locale of your source data.

Power Query relies on system or query-level locales to interpret text. By explicitly defining the locale during the conversion step, you override automatic detection and ensure accurate parsing regardless of local computer settings.

Select the column

In the Power Query Editor, locate and right-click the header of the column containing your text dates.

Change type with locale

From the context menu, select 'Change Type', and then click on 'Using Locale...' at the bottom of the list.

Configure locale settings

In the dialog box, set the Data Type to 'Date'. Then, choose the Locale that matches the structure of your source text (for example, choose 'English (United Kingdom)' if your source data is in DD/MM/YYYY format).

Apply and format

Click 'OK' to apply the true date conversion. Once converted correctly, you can reformat the resulting date column to your preferred layout.

Convert Text to Date Using Specific Locale

Avoid Auto-Detection: Do not rely on automatic data type detection or standard Excel formulas for this issue, as they default to your computer's local regional settings and will recreate the error.

Solution 2

Modify Regional Settings for the Entire Query

If your entire dataset originates from a different region, you can adjust the regional settings for the whole file to ensure all incoming dates are interpreted correctly.

Data Processing in WPS Office

Easily Parse Regional Dates in WPS Spreadsheet

You can avoid complex query configurations by using WPS Spreadsheet. Its intuitive Text to Columns feature allows you to seamlessly define the correct date formats (like DMY or MDY) when parsing text data, ensuring your regional dates are interpreted perfectly.

1. Open Data in WPS Spreadsheet: Launch WPS Office and open your workbook or import your raw data file.
2. Select the Date Column: Highlight the column containing the text dates that need accurate conversion.
3. Use Text to Columns: Navigate to the 'Data' tab on the top ribbon and click on 'Text to Columns'.
4. Specify Date Format: Proceed to Step 3 of the wizard, select 'Date', and choose the correct format (e.g., DMY) from the drop-down to match your data.
5. Complete Conversion: Click 'Finish' to instantly convert the text into accurate, calculable date values.

Free, lightweight, and user-friendly interface.Seamlessly compatible with Microsoft Excel file formats (.xlsx, .csv).Built-in Text to Columns wizard for accurate date parsing and formatting.Cross-platform support for Windows, Mac, and Linux.

Frequently Asked Questions

Why does Power Query change the days and months around in my dates?

Power Query interprets text dates based on the locale settings of your system or query. If your data is in DD/MM/YYYY format but your system is configured for MM/DD/YYYY, Power Query will swap the month and day, converting August 7 to July 8.

Can I fix incorrect dates using standard Excel formulas instead of Power Query?

While you can technically use functions like DATEVALUE in Excel, it is highly recommended to fix the issue directly in Power Query using the 'Change Type with Locale' feature. This ensures the data is strictly accurate and standardized before it loads into your spreadsheet.

Does changing the query locale affect data pulled from SharePoint?

Yes. When extracting data from SharePoint workbooks or lists, the same text-to-date locale rules apply. Configuring the correct locale during the column type conversion step in Power Query will correctly parse SharePoint text dates.

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

You can disable automatic data type detection by going to File > Options and settings > Query Options. Under 'Current Workbook', select 'Data Load' and uncheck the option to automatically detect column types and headers for unstructured sources.

Excel & Data Problems