Power Query Problems

How to Fix Date, Time, and Time-Zone Conversion Errors in Power Query

Huma Ashraf Ch Oct 22, 2026 868 views

Question details

The user is experiencing failures when converting text-based date and time values into the Date/Time/Timezone format in Power Query.

How to Fix Date, Time, and Time-Zone Conversion Errors in Power Query

Product: Power Query
Device & OS: not provided
Scenario: Importing data where date and time values are formatted as text, specifically when dealing with regional layouts or half-hour time-zone intervals.
Observed behavior: When changing the data type to Date/Time/Timezone, the conversion fails and returns errors, likely due to unrecognized regional formats, hidden spaces, or invalid characters in the source text.

Before you start

Verify the original data source to identify the exact regional format being used (e.g., DD/MM/YYYY vs MM/DD/YYYY) and check for any irregular time-zone notations before attempting to change data types.

Solution 1Recommended

Change Data Type Using Locale Settings

Use the 'Using Locale' feature to map the source data's regional format correctly, preventing parsing errors during conversion.

The most common cause of date and time conversion errors is a mismatch between the source data's regional format and your computer's default system settings. Power Query allows you to specify the exact locale of the incoming data, ensuring it is interpreted accurately.

Open Context Menu

In the Power Query Editor, right-click on the column header of your text-formatted date/time column.

Select Using Locale

Navigate to 'Change Type' in the dropdown menu and select 'Using Locale...' at the bottom of the list.

Configure Data Type and Locale

In the pop-up dialog window, set the 'Data Type' dropdown to 'Date/Time/Timezone'. Then, select the 'Locale' that matches the origin of your data (for example, English (United Kingdom) if the dates are in DD/MM/YYYY format). Click OK to apply the changes.

Format Matching Complete: By matching the source locale, Power Query can automatically interpret the text values accurately without requiring complex custom formulas.

Solution 2

Split, Clean, and Recombine Data Components

Manually separate the date, time, and time-zone elements to clean hidden spaces or invalid characters before parsing them into a unified format.

Free Microsoft Office alternative

Manage Your Spreadsheet Data Seamlessly with WPS Office

If you frequently struggle with complex data imports and formatting errors in Microsoft Office, consider WPS Office as a lightweight, highly compatible alternative. It offers a familiar interface, robust data handling tools, and seamless compatibility with all your Excel files.

1. Download and Install: Visit the official WPS Office website to download the free installer and complete the quick setup process.
2. Open Your Data File: Launch WPS Spreadsheet and open your existing .xlsx or .csv files directly without altering the original formatting.
3. Format Your Data: Use the tools found under the Data tab to easily parse text, adjust date formats, and clean your datasets.

Fully compatible with Microsoft Excel formats, including .xlsx, .xls, and .csv files.Built-in intuitive data formatting and text-to-columns tools to manage complex datasets.Free to download and use, featuring a familiar interface with no steep learning curve.Lightweight software architecture that ensures fast performance, even on older computers.

microsoft office alternative - wps office

Frequently Asked Questions

Why do date and time values import as text in Power Query?

This usually occurs when the source data contains inconsistent formatting, unrecognized time-zone notations, or regional layouts that differ from your system's default date and time settings. Power Query defaults to text when it cannot immediately parse the format.

How do I handle half-hour time-zone intervals during conversion?

For complex intervals like half-hour time zones (e.g., +05:30), ensure the text strictly follows the standard ISO 8601 format before converting. If the built-in parser fails, you may need to split the offset from the time, convert the base time, and manually add the duration using M code formulas.

What should I do if the 'Using Locale' feature still results in errors?

If locale settings fail, your data likely contains invisible characters, leading spaces, or non-standard delimiters. Use the Trim and Clean tools under the Transform tab to sanitize the text strings before attempting the data type conversion again.

Excel & Data Problems