Customized Grouping in a Pivot Table

Jim,

Are you getting your data via Access? If so I can tell you how to do this. If not, then I'd have to think about it a bit. (No guarantees though - the Access method is so much easier I'm not sure if I can think of a different way :-)) )

:I am using Excel only.
I am not a skilled Access user.
I am loading forecasted spending by date into different sheets.
I then roll up all the data into a single sheet from which I generate the pivot table.
The grouping feature works well for weeks, months and years.
I just can't get it to match my designated accounting periods

Jim,

Jim, here it is:

Jim,

Thanks to something posted by Ivan (I believe) that prompted me to think outside of the habits I've developed (re: VLOOKUP (I always use "False" so I get an exact match)) I was able to come up with the solution you need.

Just create a look-up table like so:

In the first column, "Date", enter the start and end dates for your accounting months (going down the column). In the second column, "AcctgMonth", enter the accounting month that the date corresponds to. This will be the first day of the particular month. Here's an example using the first six months of this year:

Date AcctgMonth
01/01/2002 01/01/2002
01/27/2002 01/01/2002
01/28/2002 02/01/2002
02/24/2002 02/01/2002
02/25/2002 03/01/2002
03/31/2002 03/01/2002
04/01/2002 04/01/2002
05/05/2002 05/01/2002
06/02/2002 05/01/2002
06/03/2002 06/01/2002
06/30/2002 06/01/2002

Then just insert a new column in your pivot table raw data and use this look-up table to associate your work date with the appropriate accounting month, like so:

=VLOOKUP(A2,G$1:H$12,2,TRUE)

Remember, the fourth argument needs to be TRUE or omitted.

You can add additional columns to the look-up table if you want to associate the dates with things like fiscal years and fiscal quarters (if your company's FY is not the same as a calendar year.)

This should get you where you want to be (and all without any Access at all! LOL).

take care

:I am using Excel only.

: I would like to group data entered by date into our accounting periods. : The period convention used is 4 weeks, 4 weeks and 5 weeks. : Monthly grouping and grouping by number of days won't do it for me. : Can I create my own grouping categories?

Re: Jim, here it is:

Bariloche:

It works great.
Thanks for all your help

Jim, Thanks to something posted by Ivan (I believe) that prompted me to think outside of the habits I've developed (re: VLOOKUP (I always use "False" so I get an exact match)) I was able to come up with the solution you need. Just create a look-up table like so: In the first column, "Date", enter the start and end dates for your accounting months (going down the column). In the second column, "AcctgMonth", enter the accounting month that the date corresponds to. This will be the first day of the particular month. Here's an example using the first six months of this year: Date AcctgMonth

:I am using Excel only. I am not a skilled Access user. I am loading forecasted spending by date into different sheets. I then roll up all the data into a single sheet from which I generate the pivot table. The grouping feature works well for weeks, months and years. I just can't get it to match my designated accounting periods Jim,