PBI date column not recognised as date in Excel

[pbitej]

Hi all,

I have a bunch of date columns that are recognised as dates in Power BI, however when I publish the data model and load it on Excel, the date fields are not recognised as dates and so I am not able to sort them Newest to Oldest and vice versa.

Has anyone had this issue before and/or know what the fix is? It has been bugging me for the past couple of weeks.

[pbitej]

@amitchandak  @Anonymous thank you both.

The region is set to United Kingdom on Power BI desktop and Excel, but the issue still persists.

Works completely fine in PowerBI desktop, but not on Excel - even when I format that column as Date (UK).

:s

[Anonymous]

Hi @pbitej ,

Based on my test, the issue can be caused by that the Local for the Power BI Desktop is not corresponding to the converted date value. Suppose the Local is English( United States), the valid date format is MM/DD/YYYY. If the underlying data contains value as 27/06/2022, when it's converted to date type value, as there is no month "27", the error throws out. In your scenario, please change Local value as English(Australia) which supports date format DD/MM/YYYY then click Refresh Preview button in Query Editor.

So if your underlying data is in DD/MM/YYYY, try English(Australia) in both Power BI and Excel.

If your underlying data is in MM/DD/YYYY, try English(United States) in both Power BI and Excel.

For reference: Supported languages and countries/regions for Power BI

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@pbitej , if your system format is MM/DD then you need change the system setting or take this as text and create a new column as date

https://community.powerbi.com/t5/Desktop/How-to-apply-UK-date-format-dd-mm-yyyy-in-Date-slicer/td-p/...

DD/MM/YYYY to MM/DD/YYYY
date(( right(DD__MM__YY[date],4)), (mid(DD__MM__YY[date],4,2)) ,(left(DD__MM__YY[date],2)))