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


--
jack straw
------------------------------------------------------------------------
jack straw's Profile: http://www.excelforum.com/member.php...o&userid=34482
View this thread: http://www.excelforum.com/showthread...hreadid=542428

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


--
Gary Brown
------------------------------------------------------------------------
Gary Brown's Profile: http://www.excelforum.com/member.php...o&userid=17084
View this thread: http://www.excelforum.com/showthread...hreadid=542428

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


--
jack straw
------------------------------------------------------------------------
jack straw's Profile: http://www.excelforum.com/member.php...o&userid=34482
View this thread: http://www.excelforum.com/showthread...hreadid=542428