Date Format Changing using Analyze in Excel (UK to US)

[cposthoorn]

Hi,

I have a Power BI dataset which is used as a data source in Excel (either by analyze in excel or directly). I have multiple date fields in this dataset, and looking at it in Power BI (Web + Desktop) the dates are showing as UK dates, while if I open it as a data source in excel all dates switch to the US format.

All of my settings are set to the UK locale and I have other similar datasets which don't have this issue.

How can I fix the date formatting in Excel without using separate date tables for each date field?

Many thanks!

[GilbertQ · Accepted solution]

Hi @cposthoorn 

I would make sure that your date format is set in the PBIX file so that it will then flow through correctly to the PBI reports and Excel.

Another option is to also make sure that the locale is correctly setup in Excel?

[cposthoorn]

Thanks, I've discovered what might have caused the issue. In my PBIX I used the date format "*14/03/2001 (Short Date)", but I've now changed it to use "14/03/2001 (dd/mm/yyyy)", which seems to have fixed the problem.

Still think the other date format should still have worked as my laptop and excel settings are both set to the UK locale, but this date format forces the UK date more probably.