organising dates in pivot table column

[jack straw]

I have a very simple 3 column list of data. Date, Item purchased, Cost.
I have created a simple pivot table with item as the {Row}, Date as the {column} and sum of cost as the {Data} field.
I have grouped the date columns into months by right clicking on the pivot table column heading and selecting group.
However my data covers the period 1 Jan 2005 to 31 Mar 2006. By grouping up my data into months I find that the pivot table does not see a difference between jan 05 and jan 06, feb 05 and feb 06 etc.
How can i pivot table my data and end up with 15 columns (jan 05 to Mar 06) and not 12 (jan to Dec)
Whilst on this theme is it possible to have the date shown as mmm-yy. I only seem to have the choice od dd-mmm-yy or mmm.
Many thanks
Jack

[Gary Brown]

Create a new field called year, by adding a calculated field to your existing data as follows:-

=Year(Cell containing date)

Then add this new field to your pivot table as a column (before the date), now when dates are grouped they will be forced into years and months, by the two fields within the columns.

If you don't have a format for mmm-yyyy, select custom format and type in mmm-yyyy as the format code. This format won't work, if you have grouped data into months on the pivot table

[jack straw]

Thanks for your prompt reply
Worked perfectly
Have a great day!
Jack