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).