DATES ARE HELD AS NUMERICS AND FORMATING ALLOWS YOU TO PRESENT THEM AS DATES
HOWEVER WHEN YOU USE PIVOT TABLES AND AND YOU WANT DATES GROUPED AS MONTHS
OR YEARS ITS IMPOSSIBLE AS THE SOURCE DATE ISN'T HELD THAT WAY

You could put in a helper column and do something like this

=text(a1,"yyyy") & " - " & text(a1,"mm")

Or
=Date(year(A1),month(A1),1)

--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"GIBBON" wrote:

DATES ARE HELD AS NUMERICS AND FORMATING ALLOWS YOU TO PRESENT THEM AS DATES
HOWEVER WHEN YOU USE PIVOT TABLES AND AND YOU WANT DATES GROUPED AS MONTHS
OR YEARS ITS IMPOSSIBLE AS THE SOURCE DATE ISN'T HELD THAT WAY

To extend Barb's answer. If you want the Pivot Table to group by months use
a helper column with =MONTH(A1) where a1 is the first date in your dataset

Please do not use CAPITALS; This is considered to be shouting.
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"GIBBON" wrote in message
...
DATES ARE HELD AS NUMERICS AND FORMATING ALLOWS YOU TO PRESENT THEM AS
DATES
HOWEVER WHEN YOU USE PIVOT TABLES AND AND YOU WANT DATES GROUPED AS MONTHS
OR YEARS ITS IMPOSSIBLE AS THE SOURCE DATE ISN'T HELD THAT WAY

Hi,

Excel's pivot table can group dates by month and year! To do this the dates
must be in the data area as legal Excel dates, no need for helper columns.

1. Suppose you data has Date, Dept and Amount as fields, select the data
2. Choose the command Data, PivotTable and PivotChart Report, click Next twice
3. On the 3rd step of the wizard click Layout
4. Drag the Date field to the Row area, the Dept field to the Column area,
the Amount field to the Data area
5. Click OK, Finish
6. Put your cursor in the Row field (Date) and choose the command
PivotTable, Group and Show Detail, Group
7. In the Grouping dialog box Month will be select, click on Year also (you
could also group by quarter), then click OK.

you've got what you asked for.

--
Thanks,
Shane Devenshire


"GIBBON" wrote:

DATES ARE HELD AS NUMERICS AND FORMATING ALLOWS YOU TO PRESENT THEM AS DATES
HOWEVER WHEN YOU USE PIVOT TABLES AND AND YOU WANT DATES GROUPED AS MONTHS
OR YEARS ITS IMPOSSIBLE AS THE SOURCE DATE ISN'T HELD THAT WAY