Microsoft Excel

How to Fix Mixed US and UK Dates in Power Query: 4 Clear Steps

Fix mixed US and UK dates in Excel Power Query in 4 steps by converting each date column with its correct locale and checking the results.

Mixed US and UK dates can turn into errors or, worse, valid but incorrect dates when Excel Power Query guesses the format. For example, 04/05/2024 could mean April 5 in the US or May 4 in the UK. The reliable fix is to keep the source values as text until you identify their date order, then convert each column using the matching locale. This guide is for Excel users importing CSV or text data; the result is a consistent Date column that refreshes with the query.

Prerequisites

  • Tools: Excel with Power Query and the CSV or text source file.
  • Time: About 10–20 minutes, depending on the file and number of columns.
  • Difficulty: Easy, provided you can identify the source format for each column.
  • Before you start: Make a copy of the source file or workbook, and confirm whether each date column uses month/day/year (US) or day/month/year (UK). If the values are ambiguous, check the source system or records that provide context.

Step 1: Open the query and inspect the date columns

In Excel, select a cell in the imported data and choose Data > Queries & Connections. Open the query for editing. In Power Query Editor, inspect each date column and its Applied Steps. Look for an automatic Changed Type step that converted dates before you verified their format.

For reliable diagnosis, use a value where the day is greater than 12, if available: 18/04/2024 must be day/month/year, while 04/18/2024 must be month/day/year. Values such as 04/05/2024 alone cannot tell you which convention the source intended.

Step 2: Keep ambiguous imported values as text

If Power Query has already interpreted the values, delete or edit the early automatic Changed Type step: select the step’s delete icon in Applied Steps, or use the settings to adjust the conversion. Keep the affected column as Text while you determine its source convention. This prevents an initial locale guess from silently swapping the month and day.

For a CSV or text import, also check the Source step’s file origin and locale settings. Microsoft notes that a specific Change Type locale takes precedence over the workbook and operating system locales, so changing your computer’s region alone may not fix an existing conversion step.

Step 3: Convert each column with its source locale

Right-click a date column header, choose Change Type > Using Locale, then select the Date data type and the locale matching that column’s input. Use English (United States) (en-US) for month/day/year values and English (United Kingdom) (en-GB) for day/month/year values. Repeat separately for every date column that follows a different convention.

Example: If a US-source column contains 04/05/2024, converting it with en-US yields April 5, 2024. If a UK-source column contains the same characters, using en-GB yields May 4, 2024. These are different dates despite identical text. If one column contains a mixture of conventions row by row, a single locale cannot disambiguate it; use a reliable source indicator (such as source file or region) to separate rows before converting.

Step 4: Check the converted dates and refresh

Review known examples in the preview and confirm that the resulting dates match the source meaning. Check for Error values and for month/day swaps; errors are not the only sign of a problem because a swapped date can still be valid. When the results are correct, select Home > Close & Load to return the data to Excel. Refresh the query and confirm that the dates remain correct after re-import.

For repeatable results, retain the locale-specific conversion in the query. Microsoft documents that workbook regional settings persist with the workbook, and a Change Type operation can specify its own locale. See Microsoft’s Power Query locale guidance and Power Query data type documentation.

Troubleshooting

  • Dates show errors after conversion: Confirm the source convention and choose the matching locale. Check for blanks, extra spaces, timestamps, or inconsistent formats. If formats vary by row, separate rows by a trustworthy source field first.
  • Dates convert without errors but are wrong: Use known records to check the day and month. Undo the conversion and repeat with the correct locale; a valid date does not prove the interpretation is right.
  • Refresh changes the result: Inspect Applied Steps to ensure the locale-specific conversion remains in the query and occurs after the source text is available. Check whether an earlier automatic type step is still converting the column.

Frequently asked questions

Can I set one locale for the entire workbook?

Yes. In Excel, go to Data > Get Data > Query Options > Current Workbook > Regional Settings. That sets a default, but use Using Locale on a specific column when its source uses a different convention.

What if dates use different formats in the same column?

Do not apply one locale blindly. Keep the values as text, identify each row’s format from reliable source information, split or conditionally process the groups, and convert each group with the corresponding locale. If the source provides no way to distinguish an ambiguous value such as 04/05/2024, its intended date cannot be determined from that text alone.

Does changing the displayed date format fix the underlying date?

No. Cell formatting changes how a date value appears, but it does not correct a date that Power Query interpreted with the wrong month and day. Fix the conversion in Power Query, then choose a display format in Excel if needed.

Image caption: Comparing source date strings before applying locale-aware conversion in Power Query.

More in Microsoft Excel