The Fiscal Tax Year End Problem
I need a new calculation for the financial tax year, and this why. I only recently realised that the financial tax year, in the UK, actually starts April 6th. This was new to me, I’d always though it was the 1st April. It’s true that many companies choose to start their financial year on the 1st of April but the UK tax year doesn’t, it starts on the 6th. As a result, creating a financial tax year formula using Excel becomes that little bit more difficult. The reason being is that most people will write a formula for the financial year that would look similar to.
=IF(MONTH(A2)<4,YEAR(A2)-1 & “/” & YEAR(A2), YEAR(A2) & “-” & YEAR(A2)+1)
Now this works fine if your financial year begins on the first of April. But what if it begins on the 6th of April or some other weird day in the middle of the month?
If that’s the case then there’s no need to abandon hope. With just a slight adjustment to this calculation you can return to the Excel stardom you’ve gotten used to at work.
It’s now time to introduce the DATE() function. The DATE() function basically works as follows:
=DATE(Year,Month,Day)
In the first argument in you would enter the year as a integer. The second arguement contains the month and the final argument you place the day of the month.
Let’s adjust this to select the 5th of April for any particular year. (Assuming the date is cell A2).
=DATE(YEAR(A2),4,5)
Now the above formula represents April 5th in any year. What do we need to do to work out the fiscal year from this? That’s right! We require an IF function. Let’s add one.
=IF(A2<=DATE(YEAR(A2),4,5),
Looking above at the logical part of the IF function you can see that we are comparing the value of today’s date to see if it’s less than or equal to the fifth of April. Moreover, it doesn’t matter which year we are comparing, because the YEAR function in the first arguement of the date function returns the YEAR.
Finally, we have to add the true and false values.
=IF(A2<=DATE(YEAR(A2),4,5),YEAR(A2)-1 & “-” & YEAR(A2),YEAR(A2) & “-” & YEAR(A2)+1)
And we’re done!
Want to know more? The Computer Tutoring website gives a load more detail about the Excel Financial Year Formula, including how to create a formula on how to handle the 6th April tax year issue.