UK/US Date Format Bug in PowerPivot and SSAS Tabular

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!

5 thoughts on “UK/US Date Format Bug in PowerPivot and SSAS Tabular

  1. 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…?

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

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