Excel and Financial Year Quarters

Australian solution

The Australian Financial Year runs from 1 July to 30 June. To determine which quarter a date falls into there are at least a couple of formulas that will do the trick.

If cell A1 contains a date then the following nested IF function formula will return the quarter.

`=IF(MONTH(A1)<=3,3,IF(MONTH(A1)<=6,4,IF(MONTH(A1)<=9,1,2)))`

The MONTH function simply returns the month number (1 to 12) for a date.

The above formula is an example of a nested IF function that progressively determines the result to display. Its starts with the lowest month number and works its way up.

Another function that can handle this calculation is the CHOOSE function.

When A1 contains a date this CHOOSE function will return the correct quarter.

`=CHOOSE(MONTH(A1),3,3,3,4,4,4,1,1,1,2,2,2)`

The CHOOSE function has two parts. The first is a number that specifies the sequential value to return. The second part has multiple entries that are list sequentially. These are the possible results that can be returned.

In the above case the month can be between 1 and 12. The 12 entries listed in the formula are the Financial Year quarters, in sequence, for each month from January to December.

The first three months are quarter 3, the next three are quarter 4, the next three quarter 1 and the last three quarter 2.

October is the 10th month. The tenth entry in the sequence is 2 and that is what will be returned by the CHOOSE function.

(This CHOOSE technique was inspired by a formula from the book 101 Ready To Use Excel Formulas by Michael Alexander and Dick Kusleika recently published by Wiley).

``` Related Posts Percentage of the Year in Excel Custom Function for Age in Excel MIN and MAX and Dates in Excel Financial Year Month in a Pivot Table Text for a week in Excel ```

Please note: I reserve the right to delete comments that are offensive or off-topic.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

2 thoughts on “Excel and Financial Year Quarters”

Dear Sir,

I using below formula for quarter selection, but after using this formula (=”Q” & INT ((MONTH(B2)+2)/3) & “-” & YEAR(B2))= Jan to Marc = Q1, but I need Jan to Marc Q4 and Apr to Jun = Q1.

Will you help for correct formula?

Year Quarter as per formula Correct Quarter Need collect formula
01/04/2015 Q1 Q2-2015 Q 1- 2015-2016
01/05/2015 Q2-2015 Q 1- 2015-2016
01/06/2015 Q2-2015 Q 1- 2015-2016
01/04/2015 Q2 Q2-2015 Q 2- 2015-2016
01/05/2015 Q2-2015 Q 2- 2015-2016
01/06/2015 Q2-2015 Q 2- 2015-2016
01/07/2015 Q3 Q3-2015 Q 3- 2015-2016
01/08/2015 Q3-2015 Q 3- 2015-2016
01/09/2015 Q3-2015 Q 3- 2015-2016
01/10/2015 Q4 Q4-2015 Q 4- 2015-2016
01/11/2015 Q4-2015 Q 4- 2015-2016
01/12/2015 Q4-2015 Q 4- 2015-2016

Warm Regards,
MOBILE NO.: 9167248046, Email – bharat.rotex@gmail.com

• Try this formula for the quarter.

=IF(MONTH(A1)>3,TRUNC((MONTH(A1)-1)/3,0),4)

Apologies for the delay in replying I was traveling.