'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix Power Query Date Format Errors for DD.MM.YYYY Data
Question details
The user needs to correctly parse and convert text dates formatted as DD.MM.YYYY in Power Query without triggering a DataFormat.Error.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing a dataset where dates are formatted in the European dd.mm.yyyy style, which conflicts with the system's default date settings during the Power Query import process.
- Observed behavior
- Power Query attempts to convert the text to dates but produces a DataFormat.Error on certain rows because the day value exceeds 12, causing a month-day mismatch.
Verify that your original data source is fully closed to prevent file-lock errors, and ensure the date column you intend to convert is currently formatted as 'Text' rather than 'Any' in Power Query.
Use the 'Using Locale' Feature to Convert Dates
This is the most efficient and direct method. It instructs Power Query to read the specific column using a regional setting that natively understands the DD.MM.YYYY format.
Power Query relies on your system's default regional settings to interpret dates. When importing data formatted differently than your local computer (e.g., importing European DD.MM.YYYY dates on a US machine expecting MM/DD/YYYY), it misinterprets the days and months.
By applying a specific locale only to the affected column, you bypass this mismatch without altering your overall system settings.
Open the Power Query Editor and locate the column containing your DD.MM.YYYY text dates.
Right-click the column header, hover over 'Change Type', and select 'Using Locale...' at the bottom of the context menu.
In the dialog box that appears, set the 'Data Type' dropdown to 'Date'. For the 'Locale' dropdown, select a region that uses the DD.MM.YYYY format, such as 'German (Germany)' or 'English (United Kingdom)'.
Click 'OK'. If prompted to replace the current conversion step, choose 'Replace current'. Your dates will now parse correctly without errors.
Split, Reorder, and Combine Date Components
If the data is heavily mixed or the locale settings fail, you can manually isolate the day, month, and year values using delimiters and combine them into a standard format.
Experience Hassle-Free Data Importing with WPS Office
If you are tired of encountering complex data parsing errors like DataFormat.Error in Microsoft Excel, WPS Office offers a highly compatible and user-friendly alternative. Its intuitive Text-to-Columns wizard and smart data import features make handling international date formats effortless.
- 1. Open your data file in WPS: Launch WPS Spreadsheets and open your CSV or Excel file containing the dates.
- 2. Use Text-to-Columns: Highlight the date column, navigate to the 'Data' tab, and click 'Text to Columns'.
- 3. Select the correct date format: Proceed to the final step of the wizard, choose 'Date', and select 'DMY' from the dropdown to instantly convert European dates to standard values.
Frequently Asked Questions
Why does Power Query show a DataFormat.Error for dates?
This error happens when Power Query attempts to parse text as a date based on your system's default regional settings, but the text format (e.g., DD.MM.YYYY) conflicts with the expected layout (such as MM/DD/YYYY). Dates like 15.01.2023 fail because '15' is invalid for a month.
Can I set a global default locale for Power Query imports?
Yes. You can change your default regional settings for the current workbook by going to File > Options and settings > Query Options. Under 'Current Workbook', select 'Regional Settings' and change the locale to match your standard data sources.
What if my DD.MM.YYYY dates contain a mix of periods and slashes?
Before attempting to change the data type to Date, highlight the column, right-click, and use the 'Replace Values' feature. Replace all slashes with periods (or vice versa) to ensure the delimiter is uniform, then apply the 'Using Locale' conversion step.
Will changing the locale in Power Query affect my computer's clock and date settings?
No. Utilizing the 'Using Locale' feature in Power Query applies strictly to that specific transformation step in the editor. It does not alter your Windows regional settings or the display formatting in your final Excel worksheet.
