Powerpivot incorrectly convert date value to text in pivot-table

Question

I'm using Powerpivot to create a pivot table in Excel.

In Powerpivot,the Date_Received column is correctly formatted as Date (dd/mm/yyyy) and all of the date columns are working correctly in Powerpivot. However when I try to summary data by pivot table in excel, all the date columns are converted to text and behave as text in pivottable. Consequently, I can't not group the result by Year, Month... or even sort data from Oldest to Newest.

I think it is a very common problem with Powerpivot, could someone please help?

Answer 1

My guess is that you are UK based? It's a well known and long standing issue that is connected to PowerPivot failing to properly understand the date format.

There are some things you need to do to get round this:

• Use a separate date table that has a column of unique, continous dates that cover your required period. Each other table with dates should be linked to this.
• In the PowerPivot window on the 'Design' tab mark this as your date table with your date column as 'the date'
• Change the format of this date column in the PowerPivot window to something that is non ambiguous - I tend to use yyyy-mm-dd.

At this point any pivot you create using this column will be sortable by date and the date filters will become available.