# Dates become text when making pivot table from PowerPivot



## emesgee (Mar 26, 2013)

Dates are formatted correctly (and right-aligned) within PowerPivot, but when I make a pivot table in Excel from PowerPivot data, Dates turn to text (left-aligned). 

I have seen this problem reported many times, but I've seen no remedies. I don't know how this hasn't been resolved yet, considering how crucial it is.

I'm using Office 2010. Is this problem fixed in Office 2013?


----------



## Jacob Barnett (Mar 26, 2013)

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


----------



## miguel.escobar (Mar 26, 2013)

it could also happen that the column that you're using has not been defined as part of your dates table and more specifically it hasn't been defined as THE dates column.


----------



## emesgee (Mar 26, 2013)

miguel.escobar said:


> it could also happen that the column that you're using has not been defined as part of your dates table and more specifically it hasn't been defined as THE dates column.



Cool, I didn't know you had to define a Dates column. How do you do it? Thanks.


----------



## miguel.escobar (Mar 26, 2013)

1. Open the powerpivot window
2. go to your dates table
3. go to the design tab now
4. check the MARK AS A DATE TABLE and click on it
5. a new window will pop-up and you'll need to define your DATES column within that dates table

once you do that, refresh the pivot tables and try dragging the dates columns to either rows or columns. Unfortunately, the left aligned thing will not change but..you can now filter using the Dates filter option and much more


----------



## emesgee (Mar 26, 2013)

miguel.escobar said:


> 1. Open the powerpivot window
> 2. go to your dates table
> 3. go to the design tab now
> 4. check the MARK AS A DATE TABLE and click on it
> ...



thanks, didn't know about this... but I was shot down by another limitation: no duplicates.


----------



## Jacob Barnett (Mar 26, 2013)

Rule number 1 of PowerPivot Club - always use a separate dates table that consists of unique contiguous dates.


----------

