Excel – How to calculate Australian financial year from date

by

The following Excel formula shows how to calculate an Australian financial year from a date.

For example – 10/03/2023 would return 2022-23

For example, if you have a date in cell A1 and you want to calculate the Australian financial year for that date, you can use the following formula:

=IF(MONTH(A1)<7,YEAR(A1)-1&"-"&RIGHT(YEAR(A1),2),YEAR(A1)&"-"& RIGHT(YEAR(A1)+1,2))

How does this work?

The Australian financial year starts on 1 July and ends on 30 June in the following year.

It is typically written in the format of “YYYY-YY”, for example 2022-23.

Excel does not have a native function to determine the Australian financial year from a date.

This formula achieves using the logic of:

  • if the month in cell A1 is less than 7 (i.e., before July),
    • then it returns the previous year as YYYY, followed by a hyphen, and then the last two digits of the given year as YY
  • if the month in cell A1 is 7 or greater (i.e., in July or later),
    • then it returns the current year as YYYY, followed by a hyphen, and then the last two digits of the next year as YY