Forum Discussion
Solved
Changes in format of Date columns in Pivot Table
This UK date format 11-05-2024 (General) in source data (Get from folder) is auto recognized as 11-05-2024 (Date) in Transformation/Combined), but when Closed/Loaded the date is converted to USA form...
I meant Power Query regional settings, please check them. Data->Get Data->Query Options->Regional settings->locale
done for the Excel workbook in which you run query.
I guess you transform with Power Query. What are your regional settings for it
they are here
and what are your regional settings for Windows (if you are on that platform)?
Hi, I confirmed that Windows and Excel are ser to UK English, but I can't check when I'm in the pi ot table.
Please check to be sure.
As workaround you may change type using locale from right click menu on column in Power Query
- Thanks for your help on this.
I've done more investigating:
Confirmed regional settings are correct for both Windows 11 and Microsoft 365
For CSV or XLS files, the imported date data is UK format, but the loaded data is USA format
For XLSX files, imported date data is USA format, but loaded data is UK format.
The CSV date column was Text, the XLS and XLSX were Date.
Is there a regional setting for the import process?
Incidentally, this issue only started recently - possibly with a 365 upgrade. I've been using Get Data with a wide range of data for several years.I meant Power Query regional settings, please check them. Data->Get Data->Query Options->Regional settings->locale
done for the Excel workbook in which you run query.