How to Convert UK Date Text to US Dates in Power Query
Question details
The user needs to correctly convert UK-style date and time text from a CSV file into US dates within Power Query.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Importing CSV data containing UK formatted dates (dd/mm/yy) into an environment configured for US dates (mm/dd/yy) where automatic refreshes overwrite static formulas.
- Observed behavior
- Excel incorrectly interprets UK dates, evaluating '2/6/22' as February 6th instead of June 2nd due to default US locale parsing.
Ensure your CSV data is loaded into the Power Query Editor and that the column containing the dates is currently recognized as text or an unformatted data type before applying transformations.
Change Data Type Using Locale in Power Query
This method ensures that Power Query correctly interprets the source UK dates and automatically applies the fix every time the CSV data is refreshed.
By specifying the source locale, Power Query understands exactly how to read the day and month positions. This is highly recommended over using static worksheet formulas because transformations inside Power Query run automatically during every data refresh.
Open the Power Query Editor and locate the column containing your UK date and time text.
Right-click the column header, hover over 'Change Type' in the context menu, and select 'Using Locale...' at the bottom of the list.
In the Change Type with Locale dialog box, set the Data Type to 'Date/Time' and choose 'English (United Kingdom)' as the Locale. Click OK.
Click 'Close & Load' on the Home tab to apply the transformation. The dates will now correctly display according to your system's local US format.
Extract Date Components via Custom Column
If you need to manipulate the date text manually or construct a specific text string format, you can extract the components using a custom column formula.
Handle CSV Imports Seamlessly with WPS Office
If you are dealing with complex data transformations and regional date formatting issues in Excel, consider trying WPS Office. WPS Spreadsheet offers highly intuitive text-to-column features and data import wizards, making it easy to parse different date formats without writing advanced queries.
Frequently Asked Questions
Why does Excel change my UK dates to US dates when importing a CSV?
Excel automatically uses your computer's regional settings (locale) to interpret data. If your system is set to US formats, Excel assumes the first number in a date like '2/6/22' is the month, meaning it reads it as February 6th instead of June 2nd.
Can I use the TEXT formula in Excel instead of Power Query to fix this?
While you can use a formula like =TEXT(A1, "dd/mm/yyyy") in a static spreadsheet, any time you refresh the Power Query connection, your formula outputs may be overwritten, shifted, or disrupted. It is best practice to handle the locale transformation directly inside Power Query.
What is the difference between changing the data type and using locale in Power Query?
Simply changing the data type tells Power Query to convert the text to a date using your system's default locale. 'Using Locale' allows you to specify the exact origin format of the data (like English - UK) so Power Query translates the day and month positions accurately before applying it to your workbook.
