PowerPivot Defaulting to US Date Format

Question

Hi

I have read the forums and have not found a clear answer. 

System is Win7, Office pro Plus 64Bit and Powerpivot 2012 64.

The date format in excel pivots created from Powerpivot default to US date. mm/dd/yyyyy

If I create a pivot in excel   from the same table the date formats are fine. dd/mm/yyyy
Language and date settings are correct for Australia Adelaide. (and need to be kept this way) . 

I can use a DAX to "reformat" the date but then it doesn't present as a date and causes other problems. 
PS I think this is a SQL thing because the date issue also affected my report builder projects. 

What am I missing. 

Answer 1

What's your collation set to? By default, PowerPivot uses the collation that the workbook is first created under: ie., if your collation is set to English (US), your workbook will be in US colation and date format is mm/dd/yyyy, even if you change the collation later to English (Australia).

Can you create a new workbook under English (Australia) colllation and see if it solves the problem?

Chu

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 2

Thanks for this, I had a look around and...

My system is set to english australia.
Excel dictionary language is set to English Australia.
The default date format in the wokbook under date options is dd/mm/yy (and presents this way in in a date column.)
The Linked table data in power pivot also displays in the correct fromat, dd/mm/yy, as far as I can see al other system settings are set to dd/mm/yy.

To test this I am creating a date column in a new blank workbook, then linking to power pivot then creating a pivot table.

I also have a local MS SQL Express 2012 wich is set to Latin1_General_CI_AS

I will do some more research on collation settings but any more tips would be appreciated.

Attached show the powerpivot data and the output pivot table.

Answer 3

I changed my locale to English Australia and do the following:

1. Start Excel 2010

2. Create a column of data in the spreadsheet as below:

Column1

2/13/2012

2/14/2012

2/15/2012

2/16/2012

2/17/2012

3. I mark the column as "Date"

4. I create a linked table using PowerPivot and in PowerPivot window I'm seeing them showing as

Column1
2/13/2012
2/14/2012
2/15/2012
2/16/2012
2/17/2012 but if you change the DataType in PowerPivot window from "Text" to "Date" - it will show Column1 13/02/2012 12:00:00 AM 14/02/2012 12:00:00 AM 15/02/2012 12:00:00 AM 16/02/2012 12:00:00 AM 17/02/2012 12:00:00 AM

---

-- This posting is provided "AS IS" with no warranties, and confers no rights

Answer 4

My view is that this is an 'Australian' problem or at least one that's being caused by different time/language settings.I have the same problem in Sydney!

My dates appear in the PowerPivot window correctly:

Once the date is added to a PivotTable it appears to have the wrong format:

This was not a problem in PowerPivot v1 or in the version that is embedded in the Excel 2013 Preview.

Answer 5

I kind of feel better it isnt just me.

The issue isnt date format selection, I have removed all US language options from my machine and still this happens.

Image to show that the only issue is the transition back from Powerpivot to the pivot table.

Uninstalling Powerpivot changing resetting date formats and removing US from the list of language options and reinstalling Powerpivot hasnt worked.  

I am going to have the machine rebuilt back to the Corp SOE image and reinstall. My colleagues do not have this problem. They are running office 32bit though so it may have something to do with that.

Answer 6

I am having the same problem in the UK. It's not just Australia.

Answer 7

My data comes from a Navision database. The date shows correctly in the Powerpivot window, i.e., dd/mm/yyyy, but when copied to the pivot table displays as mm/dd/yyyy. Windows region/locale, etc. settings all set up correctly.

I found the following solution to this problem:-

In the Powerpivot window change the format of the column with your date info from dd/mm/yyyy to either dd/mm/yy or dd/monthname/yy.

It worked for me with the same config you have, except I have Office 2010 Home & Business.

Good luck.

P.S. I found that this did not disrupt any data in columns that I created using the original date format of dd/mm/yyyy

Answer 8

Thanks Steve J0hns0n... It worked for me :)