Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

UK/US Date Format Bug in PowerPivot and SSAS Tabular

with 4 comments

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:

image

image

However, in an Excel PivotTable, I see dates formatted as mm/dd/yyyy:

image

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:

image

image

Here are the correctly formatted dates in a PivotTable:

image

It seems like there is already a Connect open on this issue here, so please vote to get it fixed!

Written by Chris Webb

March 21, 2013 at 9:30 am

4 Responses

Subscribe to comments with RSS.

  1. [...] This is a well known issue for non American English versions of Excel (which I'm guessing you are on). Chris's post explains the solution: UK/US Date Format Bug in PowerPivot and SSAS Tabular | Chris Webb's BI Blog [...]

  2. 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.

    Karthik

    August 14, 2013 at 5:13 am

    • 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…?

      Chris Webb

      August 17, 2013 at 10:25 pm

  3. This has also been bothering me for months – Thanks for posting a solution! :)

    Andy

    June 6, 2014 at 12:40 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,025 other followers

%d bloggers like this: