Date formatting for Pivot tables

Hi all, apologies if this is covered already.

I am trying to find out how to format a date so that it groups it by month in a pivot table. e.g

if I have several dates 21/02/08, 13/02/08 and so on I want the pivot table to display the data as Feb 08.

I have added another cell next to te current date e.g 13/02/08 and tried a different dispaly format like the US Month-year split however the pivot table is still picking up the serial number and displaying 13/02/08 rather than Feb-08

Any ideas?

Thanks

Hi Matt,

The problem is with your understanding of the concepts (if you don;t mind me saying so). The point of formatting is that it is a mask over existing data to show the data in the way you like. So if you have the dates from 1st - 28th Feb and format the cells "mmmm-yy" you will just see February 28 times - but the numbers haven't gone anywhere.

This means if you format your source data for a pivot table, Excel still stores the true dates in the pivot cache - it doesn't care how you've asked it to display the data, they are still numbers...

In terms of achieving what you're trying to achieve - via source data you would have to add a column which "simplifies" the raw data to monthly groups - for example =date(year(<original>),month(<original>),1) which would standardise every date entry to the first of its month. Far superior to this though, is to create the pivot table as before, then right-click on the field button ->group and outline -> group -> then select your options; this shuold be quite self-explanatory.

HTH

Great, thanks for your help