» PivotTable report - Grouping the Date Field by Quarters in a Fiscal Reporting Year

CATEGORY - Excel Pivot Tables VERSION - All Microsoft Excel Versions

To group by quarters when reporting on a fiscal year basis:

1. Add a new column to the source data (column G in the screenshot), and enter the formula below to calculate the quarter number for the fiscal year:
="Q"&(MOD(CEILING(22+MONTH(F2)-9-1,3)/3,4)+1)

2. Add a new column to the source data (column H in the screenshot), and insert the formula below to calculate the fiscal year number:
=IF(MONTH(F2)<=9,YEAR(F2),YEAR(F2)+1)

Note:
The number 9 in the formulas is the fiscal year month end (September).
Screenshot // PivotTable report - Grouping the Date Field by Quarters in a Fiscal Reporting Year