Tax Year Excel Formula

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 6^th 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 6^th April. Between the 1^st January and the 5^th 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 6^th 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))

• 14 Apr
• Tobias Owen
• Excel
•  1 Comment
•  1

• Excel , Formula , Tax Year