pivot with calculated date

[rexmann]

Hi All

I have a pivot table which I want to break the data down into month and
year. The data in the main spreadsheet is in day, month and year. So far I
have used the Month (field) and year (field) to translate the month data
within the original source file and then run the pivot table.

Is it possible to do this calculation within the Pivot table - I have tried
the inserting a calculated field but this will not allow me to put the drop
column fields here -

Any help greatly appreciated

Cheers Rexmann

PS I am using Excel 2003

 

[ShaneDevenshire]

Hi,

If you put the Year field in the Row area and the Month field to its right
in the row area, Excel will automatically calculate subtotals by month and
year. If they aren't showing, right click in the year field and choose Field
Setting, turn on Automatic or Sum under Subtotals. Repeat for Month field.

If you had the dates as dates in a single field of your data source you
could have Excel do all your work for you in one easy step. But once you
have dates broken into month, day, and year columns you can't avail yourself
of this wonderful feature.

If this helps, please click the Yes button.