Forum Discussion
Power Query inserts wrong dates to Excel
Power Query recognises dates from external source in format set in PQ locale setting for the file, or in ISO format. In your case is the latest, dates shall be recognised correctly.
In theory PQ editor shows dates in you computer locale setting format. But recently it starts to show in US format, not sure that's bug or feature. In any case it doesn't matter in which format editor shows the date.
Returned into the grid dates will be shown as in your regional format settings, or in format which you will apply to the range.
It looks like in your case the main problem is date system. PQ uses 1900 date system and your computer is on 1904 date system. Thus dates in year 2024 are shown as dates in year 2028.
I'm not Mac user and not sure if PQ on Mac recognises date system. But if you shift on 1900 these dates shall be shown correctly. Another story other dates in your files will be wrong.
So, above just a guess, perhaps someone who works on Mac could give more details.
I must admit, I was surprised to see the US/Liberia date format as the result of importing ISO dates in text format.
PeterBartholomew1 , what exactly do you mean under importing ISO dates in text format ?
I simple meant a text string (CSV file or text field in Excel) in the format "yyyy-mm-dd" rather than being held as numbers, as is usual for Excel calculation. On my machine PQ recognises these as dates and displays them as non-US locale values.
