Grouping by month in a Pivot Table

[John]

Grouping by month in a Pivot Table

I am aware of the grouping by month feature in a Pivot Table. However, I
would like to be able to group data by our accounting months, which end on a
Friday, not the last day of a month. Is there a way of defining months that
match our Accounts calendar ?

TIA
John.

 

[Roger Govier]

Hi John

Are you running 13 x 28 day periods for your accounting, or are you running
12 periods of 4, 4 and 5 weeks duration?
If the former, then in the PT Group function, Group by Days>28

If the latter, then you will need to have a formula in an extra column of
your source table, which determines the period number and use that in the PT
and ignore grouping within the PT.
Create a simple Lookup table with the last date of each accounting period in
column 1, and period number if column 2.
Give this table a name e.g. Periods
In your new column in the source table enter the formula
=VLOOKUP(A2,Periods,2)
assuming Date to be held in A2