UK/US Date Format Bug in PowerPivot and SSAS Tabular

Reposted from Chris Webb's blog with the author's permission.

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!

Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.wordpress.com/ .