Pivot Table Group by Month Beforehand

[Guest]

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]

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?

 

[Veerachai W.]

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

Veerachai W.

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?

 

[Guest]

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

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 created2003. 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