I don’t usually blog about bugs, but this one has been irritating me no end for the last year – so I thought it deserved some publicity…
In Excel 2010 PowerPivot and and in SSAS 2012 Tabular models (but not the Excel 2013 Data Model interestingly), if you have an English locale that is not US English (eg UK or Australian English), you may find that date columns appear to be formatted correctly as dd/mm/yyyy inside the PowerPivot window or in SSDT, but when you get to Excel you see the dates formatted in the US mm/dd/yyyy format. So, for example, on my laptop if I import the DimDate table from Adventure Works into Excel 2010 then I see dates formatted as dd/mm/yyyy as I’ve specified in the Formatting section of the ribbon in the PowerPivot window:
However, in an Excel PivotTable, I see dates formatted as mm/dd/yyyy:
There is a workaround though, which I found on the PowerPivot forum (thank you Steve Johnson, if you’re reading) – you can get the dates to format correctly if you go to More Date Formats and choose dd/MM/yy or one of the other formats from the dropdown list that appears:
Here are the correctly formatted dates in a PivotTable:
It seems like there is already a Connect open on this issue here, so please vote to get it fixed!
Hi Chris,
What’s the workaround for the same issue in the SSAS Tabular model? The SSAS tabular model doesn’t support dd/mm/yy format – this option is not available in the list of date formats for attributes of “Date” data type but have managed to find a work around by changing the regional date settings short date format to “dd/mm/yy”. The date data in the tabular model is showing in “DD/MM/YY” format.
When we connect to the tabular model from Excel through pivot tables it’s still showing the dates in the US format. Also, when we sorting the date attribute in pivot table doesn’t seem to work either. It looks like there is bug already reported in Connect for the date sorting.
http://connect.microsoft.com/SQLServer/feedback/details/725452/unable-to-sort-dates-win-pivot-tables-using-bism-tabular-models-as-source
Is there a workaround to change the format to DD/MM/YY in the pivot table? We are using Excel 2010.
Thanks,
Karthik.
The dd/mm/yy format is available in the list of values for the Date Format property in Visual Studio for me, and with that set everything works fine in Excel. I don’t know why it isn’t appearing in Visual Studio for you…?
This has also been bothering me for months – Thanks for posting a solution! 🙂
Hi,
I had this problem and it’s my aswer:
1) I Changed the default language/locale of SQL Server (SET Language British). More information:
https://coolkidsdoit.wordpress.com/2009/01/28/changing-the-default-languagelocale-sql-2005/
2) After first step, I changed Date Format of column in Tabular Model (SQL Server Data Tools – open Tabular Model – click on your column in table – Properties -Date Format – change the needed format (no General) ).
After this changes, I import data from model in excel (pivot table) – I get needed format of date column.