# Pivot Table Group by Quarter beginning Jul

#### BDrew

##### Board Regular
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
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
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
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

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
Sorry - yes the formula posted is complete garbage!

=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
Thank you DonkeyOte, your code works as elegantly as it looks.

regards

BD

#### JSpoon

##### New Member
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.

Replies
0
Views
134
Replies
9
Views
506
Replies
1
Views
155
Replies
1
Views
165
Replies
3
Views
58

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.

### Which adblocker are you using?

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

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