=if(A1-date(year(A1),4,5)>0,year(A1)&"/"&year(A1)+1,year(A1)-1&"/"&year(A1))&" tax year"
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
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
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.