Errors when changing data type to Date

[carlbh]

I wonder if anyone can help. I have just noticed after importing a whole load of data from excel, that PBI is converting my date information to US format when the month is greater than the day.

The data is exported into excel from another source. I have tried setting the data in the excel files to bothe date format and custom format dd/mm/yyy hh:ss before saving and importing. It always appears in PBI as Text format. So when I try to set the column to data type "date" I naively assumed it was doing it correctly. It was only when I tried to do some sorting to look for entries more than 6 months old and cross checked wotht he original spreadsheet that I spotted this issue.

For example it was converting 05/12/2018 to 12/05/2018.

I have checked the locale settings in the PBI file and the associated Query editor and both are set to UK.

[Anonymous]

Hi @carlbh,

Assuming that all the dates are formatted the same way (day/month/year) you could use this code to create a custom column:

New_Date = DATE(RIGHT([date],4),MID([date],3,6),LEFT([date],2))

You can change the datatype of New_Date to date.

Kind regards,

[Anonymous]

I literally created an account just to thank you!

[carlbh]

I am hoping there is a more central solution to this issue, I dont want to have to keep addding a new column like this every time I create a new report.

This is pretty fundamental, surely there is a microsoft solution out there......

[v-chuncz-msft · Accepted solution]

@carlbh,

You may take a look at https://answers.microsoft.com/en-us/windows/forum/all/win-10-date-format-problem-i-need-ddmmyy-date/....