Pivot Table Grouping by Quarter

  • Thread starter Thread starter Attila Fust
  • Start date Start date
A

Attila Fust

I want to group my data based on Quarter but do not want
it to be based on the standard calendar quarters (ie. Jan1-
Mar31, Apr1-Jun30,Jul1-Sep30,Oct1-Dec31) but based on the
first month in my data.

For example, the starting month in my data is Aug1 so I
would like my quarters to be base on:

Aug1-Oct31, Nov1-Jan31, Feb1-Apr30, May1-Jul31

I can't seem to get Excel to do this with the Pivot Table
grouping options. Is it possible? If not, what is my
best option. Create a query in Access?

Thanks in advance.

Attila Fust
 
Hi
for this I would create a helper column in your source data which
calculates your 'quarter' and use this helper column for grouping in
the pivot table
 
You would need to add an extra range to your table
using formulas
AFAIK it will always group after calendar quarters
 
By helper column do you mean a code. For example, for the
months Aug - Oct the month code would be "Q1" for example.

Attila Fust
 
You can calculate this in a new field in the source data table, then add
that field to the pivot table. For example, with your dates in column A,
insert a blank column to the right.
Add a heading in cell B1, such as "Quarter"
Then enter the following formula in cell B2:

=YEAR(A2)+IF(MONTH(A2)>=8,1,0)&"-"&CHOOSE(MONTH(A2),2,3,3,3,4,4,4,1,1,1,2,2)

Copy the formula down to the last row of data.
Refresh the pivot table, and add the new field.
 
Back
Top