If you have created a Pivot Table with individual dates as columns, you can right-click on the date field and choose group by month (or quarter, etc.). But it seems that you can only do that AFTER the Pivot Table has already been created. My problem is that I need to accomplish this BEFORE the Pivot Table is created

I have a database of about 5,000 records, all with individual dates for 2003. I want to create a Pivot Table grouped by month. However, because there are 365 days in a year, and because Excel only has 255 columns, I get a message telling me there is not enough room to create the Pivot Table. Consequently, I can't even get to the point where I can right-click on the date field to group by month because I can't create the Pivot Table to begin with

Is there some way of grouping by month (even if it's through VBA) prior to creating the Pivot Table so that it comes out already grouped by month

Many thanks
Paul

I had a similiar problem. I couldn't get VBA to Ungroup
PivotFields. My solution was to create a "Month" field in
the database, then just use the field "Month" in your
field layout.

Not exactly the solution you wanted..I know, but I hope it
helps.

-Brad
-----Original Message-----
If you have created a Pivot Table with individual dates
as columns, you can right-click on the date field and
choose group by month (or quarter, etc.). But it seems
that you can only do that AFTER the Pivot Table has
already been created. My problem is that I need to
accomplish this BEFORE the Pivot Table is created.

I have a database of about 5,000 records, all with
individual dates for 2003. I want to create a Pivot Table
grouped by month. However, because there are 365 days in
a year, and because Excel only has 255 columns, I get a
message telling me there is not enough room to create the
Pivot Table. Consequently, I can't even get to the point
where I can right-click on the date field to group by
month because I can't create the Pivot Table to begin with.

Is there some way of grouping by month (even if it's
through VBA) prior to creating the Pivot Table so that it
comes out already grouped by month?

Many thanks,
Paul
.

Why don't you group date on row field and then move to column?

Veerachai W.

"Paul Simon" wrote in message
...
If you have created a Pivot Table with individual dates as columns, you
can right-click on the date field and choose group by month (or quarter,
etc.). But it seems that you can only do that AFTER the Pivot Table has
already been created. My problem is that I need to accomplish this BEFORE
the Pivot Table is created.

I have a database of about 5,000 records, all with individual dates for
2003. I want to create a Pivot Table grouped by month. However, because
there are 365 days in a year, and because Excel only has 255 columns, I get
a message telling me there is not enough room to create the Pivot Table.
Consequently, I can't even get to the point where I can right-click on the
date field to group by month because I can't create the Pivot Table to begin
with.

Is there some way of grouping by month (even if it's through VBA) prior to
creating the Pivot Table so that it comes out already grouped by month?

Many thanks,
Paul

Brad
Simple yet excellent solution. Don't know why I didn't think of it myself. Works great

Veerachai
This is a perfect solution to my problem. Again, I don't know why I didn't thin
of such a simple solution myself. It works perfectly and is exactly what I was lookin
for. Now I can group on other periods (like Quarterly) as well

Thank you Brad and Veerachai for your great solutions and for taking the time t
help me out on this. I apperciate it very much

Pau

----- Veerachai W. wrote: ----

Why don't you group date on row field and then move to column

Veerachai W

"Paul Simon" wrote in messag
..
If you have created a Pivot Table with individual dates as columns, yo
can right-click on the date field and choose group by month (or quarter
etc.). But it seems that you can only do that AFTER the Pivot Table ha
already been created. My problem is that I need to accomplish this BEFOR
the Pivot Table is created
I have a database of about 5,000 records, all with individual dates fo
2003. I want to create a Pivot Table grouped by month. However, becaus
there are 365 days in a year, and because Excel only has 255 columns, I ge
a message telling me there is not enough room to create the Pivot Table
Consequently, I can't even get to the point where I can right-click on th
date field to group by month because I can't create the Pivot Table to begi
with
Is there some way of grouping by month (even if it's through VBA) prior t
creating the Pivot Table so that it comes out already grouped by month
Many thanks
Pau