0

I have a Date column. Here in the UK the tax year runs from 6th April - 5th April so the Tax year 6th April 2019 - 5th April 2020 might be referenced to as the 2019/20 tax year.

I would like to be able to add a column Tax Year which writes values like "2019/20" or "2019/2020" based on Date, how can I do this?

3
  • Please add a brief description of your search/research efforts as is suggested in How to Ask. – Rubén Dec 6 '20 at 18:49
  • Is "5th April 2010" in the question a typo? I'm pretty sure one of the two dates in the range is wrong but I'm not positive which one. I'll just proceed assuming that it should be 2020 because otherwise I don't understand the question. – Kwilinsi Dec 7 '20 at 3:55
  • Not really @Rubén I had no idea where to even start though I was sure it was possible. – Mr. Boy Dec 8 '20 at 16:33
2

Short answer

=if(A1-date(year(A1),4,5)>0,year(A1)&"/"&year(A1)+1,year(A1)-1&"/"&year(A1))&" tax year"

Long answer

If I'm understanding your question correctly, it sounds like all you're asking is this: I want a formula that takes a given date as input. If the date is before April 6th, it returns the year from that date and the previous year. Otherwise, it returns the year from that date and the next year. For example, April 3rd 2020 would return 2019/2020, and May 8th 2013 would return 2013/2014.

Let's assume that the input date is in cell A1 and we're writing a formula in cell B1. We need to figure out if A1 is after April 5th or not. We'll just take A1 and subtract the date 04/05/yyyy and see if we get a number bigger than 0 (indicating that A1 is after April 5th). The =date() formula allows us to convert a day, month, and year into a date. We'll use =date(year(A1),4,5) to get the date April 5th of whatever year A1 is in. For example, if A1 is May 2, 2012, then our formula would produce April 5th, 2012. Simple enough.

Now we just subtract the date we got from A1 and see if it's greater than 0 (which would mean that A1 came after April 5th). =A1-date(year(A1),4,5) accomplishes this nicely. We can throw this in an if statement like so

=if(A1-date(year(A1),4,5)>0,"it's after april 5th","it's the same or before april 5th")

And finally, we can just replace those strings saying whether or not it's after April 5th with the desired tax year. If it's after April 5th, we'll use =year(A1)&"/"&year(A1)+1. This is just saying "copy the year from A1, put a slash, and then the year after A1." If it's before April 5th, we'll use =year(A1)-1&"/"&year(A1), which does pretty much the same thing except it uses the previous year instead of the next year.

And that's about it. Now we have a finished formula. Now, you mentioned that you're wanting to add an entire column called Tax Year. This implies that you have a column of dates (let's call it column A) and you want a column of tax years (column B, for example). I'll assume you have a header row and your data starts in row 2 and goes to the bottom of the sheet. You could use an arrayformula in cell B2 to populate all the tax years without having to copy this formula a bunch of times.

=arrayformula(if(A2:A-date(year(A2:A),4,5)>0,year(A2:A)&"/"&year(A2:A)+1,year(A2:A)-1&"/"&year(A2:A))&" tax year")

What I've done here is simply replace every instance of A1 with A2:A to reference the entire column, and added =arrayformula() around the whole thing. Now, this is almost perfect, however if you have blank cells in A1 at the bottom of the sheet, the formula will try to calculate tax years for those too. It'll actually try to tell you that the tax year for a blank cell is 1898/1899. We can fix this by adding another =if() statement to the formula that checks to make sure the cell has a date in it first. If it doesn't, we'll just return a blank cell "".

=arrayformula(if(A2:A,if(A2:A-date(year(A2:A),4,5)>0,year(A2:A)&"/"&year(A2:A)+1,year(A2:A)-1&"/"&year(A2:A))&" tax year",""))

And that's it. You might be wondering why I only do =if(A2:A) here instead of something like if(isdate(A2:A), since that would be more ideal as it wouldn't try to show a tax year for a non date value. That would in fact be better, but unfortunately the =isdate() formula doesn't play nicely with =arrayformula(). You could use it if you were copy and pasting, but not with the arrayformula solution.

1
  • Quick tests show it works perfectly and the detail means I understand how to tweak or do something similar myself rather than slavishly copy a script - accepted and thanks very much. I had not anticipated this being so complicated! – Mr. Boy Dec 7 '20 at 9:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.