To get the Australian financial year from a date you usually use an IF function based on the month number.
I recently learned a new hack from Matt Allington of Exceleratorbi.
(Have since found out Matt learned the technique from David Marriott)
You can add 184 to the date and then use the YEAR function. See table and formulas below.
The formula in cell B2 is
=YEAR(A2)
The formula in cell C2 is
=YEAR(A2+184)
Both formulas have been copied down.
A simple solution to a frustrating issue. Thanks Matt.
I USE
=IF(MONTH(TODAY())<=6,MONTH(TODAY())+6,MONTH(TODAY())-6)
for example , this will return 10 for April etc
can be edited to show a month name also
Thanks for sharing Brendan.
A slightly shorter version of your financial year month is
=MONTH(TODAY())+IF(MONTH(TODAY())<=6,6,-6) One less MONTH function.
Actually it was me who told Matt about that formula.
If you read his blog post you’ll see he credits me as telling him about it.
Hi David
Sorry – I heard if from Matt at a Perth presentation. I haven’t seen his post on it.
I will add your name to the post.
Regards
Neale