Scenario
We wanted to create Expenses capture forms that automatically calculated the tax year for the date against each entry. We couldn’t find a readily available tax year Excel formula, so decided to write our own.
The tax year is a combination of two years, e.g. 2015-2016. For most of the year (i.e. after 6th April, when the new tax year begins), that combination is the “current year” and the “current year +1”. This can be expressed using simple concatenation with the following Excel formula:
We wanted specifically the last two digits of the year to comprise our tax year Excel formula. We achieved this using the RIGHT() function.
However, this will not always result in the correct tax year because the tax year begins on the 6th April. Between the 1st January and the 5th April, the combination for the new tax year is the “current year -1” and the “current year”. E.g. January 2015 is part of the 2014-2015 tax year.
We therefore needed to create conditions to tell Excel which combination to display. This can be achieved with a simple IF() statement. The rules we came up with are as follows:-
- Display “current year -1” and “current year” if:
- Month is Jan, Feb or Mar; OR
- Month is Apr AND Day is less than 6
- Else display “current year” and “current year +1”
The first bullet point above requires an OR(AND()) combo, as below:
Combining this condition with the tax year notation formula above, we ended up with the following:-
This formula will always calculate the correct tax year from any given date, e.g.
Download the Workbook
Tax Year (Excel) workbook
Tax Year Excel Formula
Formula Breakdown
This formula is a simple IF() statement with the following conditions:-
Logical Test
=IF(OR(MONTH(B3)<4,AND(MONTH(B3)=4,DAY(B3)<6)),
Check whether the target date is before 6th April
TRUE statement
RIGHT(YEAR(B3)-1,2)&"-"&RIGHT(YEAR(B3),2),
Use combination “current year -1” and “current year”
FALSE statement
RIGHT(YEAR(B3),2)&"-"&RIGHT(YEAR(B3)+1,2))
Use combination “current year” and “current year +1”
Get the Formula
The full formula featured in this blog is as follows:-
=IF(OR(MONTH(B3)<4,AND(MONTH(B3)=4,DAY(B3)<6)),RIGHT(YEAR(B3)-1,2)&"-"&RIGHT(YEAR(B3),2),RIGHT(YEAR(B3),2)&"-"&RIGHT(YEAR(B3)+1,2))
Comments