Excel Snippets: Get First and Last Dates of UK Tax Year

It doesn’t come up very often as I do a lot of work using SQL Server rather than Microsoft Excel, but every so often I do need to do some date manipulation in Excel.

In the previous article of the Excel Snippets series, I showed how to get the first and last dates of the calendar year. One of the peculiarities of the UK, is that our tax year always starts on the 6^th of April.

The formula to accurately get the start of the tax year is more involved than getting the sart of a calendar year, but we can break it down to make it easier to understand.

To start with we get the year of the supplied date using the YEAR function:

=YEAR( TODAY() )

Then we check if the supplied date is before the 5^th April of that year:

=TODAY() <= DATE( YEAR( TODAY() ), 4, 5 )

The date function is supplied three parameters: year, month and day, which in the above example is the output of the YEAR function, 4 and 5 for the 5^th April.

This will return TRUE if it is and FALSE if it isn’t, which will effectively return 1 or 0; in the formula we subtract this from the result of the YEAR function which will give us the correct year:

=YEAR( TODAY() ) - ( TODAY() <= DATE( YEAR( TODAY() ), 4, 5 ) )

We can then wrap this in another DATE function which supplies 4 and 6 for the month and day to output the first day of the tax year:

=DATE( YEAR( TODAY() ) - ( TODAY() <= DATE( YEAR( TODAY() ), 4, 5 ) ), 4, 6 )

To get the last day of the UK tax year, we just need to copy the above formula, add a +1 to the output of the first YEAR function and change the day parameter in the outer most DATE function to 5[/:

=DATE( YEAR( TODAY() ) + 1 - ( TODAY() <= DATE( YEAR( TODAY() ), 4, 5 ) ), 4, 5 )

Updated 13/06/2023: After my error was pointed out, I’ve done some investigating and updated the post above; the formula here now is one posted by Brad Scott in response to a question on a Microsoft Community question.