• open dock
  • Home
  • Professional Services
    • Business Improvement
    • Data Optimisation Services
  • Our Approach
    • maBI
    • Data-Driven Decision Model
  • Portfolio
    • Case Studies and Portfolio
    • Timeline
  • Contact Us
Change this Limited
  • Home
  • Professional Services
    • Business Improvement
    • Data Optimisation Services
  • Our Approach
    • maBI
    • Data-Driven Decision Model
  • Portfolio
    • Case Studies and Portfolio
    • Timeline
  • Contact Us

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 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:

01
We wanted specifically the last two digits of the year to comprise our tax year Excel formula. We achieved this using the RIGHT() function.

02

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:

03

Combining this condition with the tax year notation formula above, we ended up with the following:-

04

This formula will always calculate the correct tax year from any given date, e.g.

05

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))
  • 14 Apr
  • Tobias Owen
  • Excel
  •  1 Comment
  •  1
  • Excel , Formula , Tax Year

Share This

Related Posts

  • Calculate Time Elapsed using NETWORKDAYS()
  • Why INDEX-MATCH is better than VLOOKUP
  • Rock Paper Scissors
  • Rank by Multiple Criteria in Excel
Organisational Announcement →← Why INDEX-MATCH is better than VLOOKUP
0

Comments

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

  • Recent Posts

    • Change this Christmas Dash-er-board
    • Expo round-up and prize draw answers
    • Change this at Birmingham Business Expo – 14 Nov 2017
    • Power BI Custom Visual – Play Axis – Review
    • File Naming Conventions and Best Practice
  • Archives

    • December 2018
    • November 2017
    • June 2017
    • March 2017
    • February 2017
    • January 2017
    • December 2016
    • November 2016
    • September 2016
    • August 2016
    • July 2016
    • May 2016
    • April 2016
    • March 2016
    • February 2016
    • January 2016
    • December 2015
    • September 2015
    • June 2015
    • May 2015
    • April 2015
  • Categories

    • Case Studies
    • Excel
    • Insight
    • News
    • Portfolio
    • Power BI
    • SAP Dashboards
    • SharePoint
  • Search our site

  • Social

  • Recent Blog Posts

    • Change this Christmas Dash-er-board
    • Expo round-up and prize draw answers
    • Change this at Birmingham Business Expo – 14 Nov 2017
    • Power BI Custom Visual – Play Axis – Review
    • File Naming Conventions and Best Practice
  • © 2016 Change this Limited
    Website by Change this
    Standard Terms of Business
    Privacy Policy

    Delivering the difference

This website uses some cookies to improve your experience within the website.


We also use cookies to understand how visitors use our website Accept


To find out what cookies we use and for more information, Read More.
Privacy & Cookies Policy

Necessary Always Enabled

Non-necessary