# Excel 2007 - Change date format for pivot tables



## NCFC (Sep 17, 2008)

Hi

Can someone show me how to covert a date e.g. dd/mm/yyyy so that it reads as mm/yyyy only and so that a pivot table doesn't recognise the orignal long date of dd/mm/yyyy.

Tried using custom to mm/yyyy but pivot table still reads the date as dd/mm/yyyy.

Thanks

NCFC (Newbie)


----------



## Yorkshire Guy (Dec 9, 2003)

hi NCFC,

Don't know about Excel 2007, but in 2003:

Right Click the Pivot Table,
Field Settings,
Select the field name that holds the dates,
Number,
Choose your format.

lol
Hew


----------



## Nic Cunliffe (Sep 18, 2008)

Hi NCFC,

I have this problem a lot and found the best solution is to create two additional cols in the base data. Using example date 19/09/2008, first col formula: =month(a2) this returns '09'
2nd addition col =year(a2) this returns '2008' or '08'

You can then dray the year and month into the pivot. I find year first is best if your date range is spans years.

Regards - Nic


----------

