Login

  • Mar 11

Excel and Financial Years and Months

  • Neale Blackwood
  • 0 comments

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.

Sign up to hear about free Excel training.

I won't share your email with anyone.

  • 2 mins read

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

This 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))*-12

This adjusts the month number based on the financial year end month number and if the month is less than the financial year month number.

0 comments

Sign upor login to leave a comment