Excel is built to work with calendar years. Financial years pose a few issues for correctly identifying year and month numbering. I wanted to create some formulas that would work with all financial year ends. Here they are.
In the image below we have calculations for financial year and financial year month number for Australia and India.
Previous posts
I have covered some of this before in separate blog posts.
Days adjustment
The formula in cell B1 calculates the days adjustment for the financial year.
This number is added to a date to convert the year number of the date to the financial year number of the date. It is calculated by deducting the financial year end from 31 December in the same year.
The formula in cell B1 is.
=DATE(YEAR(B2),12,31)-B2This has been copied down and across column C. This calculates the difference in days between the financial year end and the calendar year end.
The formula in cell B6 is.
=YEAR($A6+B$1)The adjustment value is added to the date. The YEAR function uses the adjusted date to calculate the financial year.
Financial year month number
The formula in cell E6 that has been copied across and down is.
=MONTH($A6)-MONTH(B$2)-(MONTH($A6)<=MONTH(B$2))*-12This adjusts the month number based on the financial year end month number and if the month is less than the financial year month number.