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:
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