Quarter periods

Copper Contributor

Hi everyone, 

 

I am trying to get excel to group my quarters for the academic year (Sept - Aug) rather than by calendar year (Jan-Dec) in my pivot tables. I can't find anything online and I have failed with my experiments. Any suggestions? 

1 Reply

@CharlotteLB 

I'd add a helper column to the data named Quarter.

Let's say your dates are in A2 and down.

In row 2 in the helper column:

=MOD(QUOTIENT(MONTH(A2)+3,3),4)+1

Fill down.

If your data are in a table, with a column named Date, you can use

=MOD(QUOTIENT(MONTH([@Date])+3,3),4)+1

Make sure that you include the new column in the source range of the pivot table.

Add the Quarter column to (for example) the Rows area.

 

If you wish, you can also add a column Academic Year:

=LET(y, YEAR(EDATE(A2, -8)), y&"/"&y+1)

or

=LET(y, YEAR(EDATE([@Date], -8)), y&"/"&y+1)

 

HansVogelaar_0-1712570868548.png