Pivot Table Group by Quarter beginning Jul

BDrew

Board Regular
Joined
Jun 8, 2008
Messages
79
I have looked everywhere for the simple solution to Grouping my Pivot Table Months by Quarter beginning July not January.

I would appreciate any assistance.

regards

BD
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
You could add a column at source to assign the appropriate Quarter, eg:

B1: QTR
B2: =LOOKUP(MATCH(MONTH(A2),{1,4,7,10}),{3,4,1,2})
copied down - where A2 holds date

and use QTR as your PT Field rather than grouping by Month.

AFAIK there's no way of amending the PT's quarter assumptions (ie Calendar).
 

litrelord

Well-known Member
Joined
Dec 1, 2002
Messages
519
Hi,

do the months Jan to Jun belong to the following year with a 12 moth period running Jul to Jun?

If this is the case then the best way would be to use dates instead of just months, this way Excel will see the year as well and will order them properly. You can change the format of the cells in the pivot table to show only the month if you need to keep the current formatting.

Alternatively you can right click on the row or column headers int he pivot table and select move to change the order they're in.

HTH

Nick
 

Robert E Lee

Active Member
Joined
Aug 10, 2005
Messages
266
Hi

you could create a custom list JUL,OCT,JAN,APR and then sort the row, or column, according to this list

HTH

Robert
 

BDrew

Board Regular
Joined
Jun 8, 2008
Messages
79

ADVERTISEMENT

Thanks everyone.

I tried them all.

Finally I placed the QTR field on the database and I am up to 3mb, mostly of field labels.

The array formula produced QTR 3 for July instead of Qtr 1. Pity, it looked like an elegant solution.

regards

BD
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Sorry - yes the formula posted is complete garbage!

Should read:

=LOOKUP(MONTH(A2),{1,4,7,10},{3,4,1,2})

or

=INDEX({3,4,1,2},MATCH(MONTH(A2),{1,4,7,10}))

Note: the above are not array formulae - though they do make use of inline array constants { } rather than ranges.
 

BDrew

Board Regular
Joined
Jun 8, 2008
Messages
79
Thank you DonkeyOte, your code works as elegantly as it looks.

regards

BD
 

JSpoon

New Member
Joined
May 13, 2016
Messages
1
I have looked everywhere for the simple solution to Grouping my Pivot Table Months by Quarter beginning July not January.

I would appreciate any assistance.

regards

BD


Hi, if you use the default group by quarter in the pivot table, you can overkey the quarters to read as you want e.g. Qtr 3 (July to September) can be overkeyed to 1 and so on.
 

Forum statistics

Threads
1,136,329
Messages
5,675,146
Members
419,552
Latest member
jsanjur

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top