google sheets
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?
If you add the following formula in C7 with sum written in B7, then the total will be calculated the way you want:
=SUM(ARRAYFORMULA(IF(A2:A6=1;C2:C6-B2:B6,0)))
The following formula will help you filter the dates.
=FILTER(sourceArray, arrayCondition_1,..., arrayCondition_30)
=SUM(FILTER(B2:B, RIGHT(A2:A, 4)="2012", LEFT(A2:A,LEN(A2:A)-5)="february"))
The first arrayCondition_1 retrieves the year, having 4 digits, taken from the RIGHT. The second arrayCondition_2 retrieves the month (text), based on the total length of the string minus 5 (digits year plus white space). The sourceArray in filtered accordingly and the result is summed up.
I've created an example file for you: FILTER DATE AS TEXT
Best Answer
Short answer
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
A1and we're writing a formula in cellB1. We need to figure out if A1 is after April 5th or not. We'll just takeA1and subtract the date 04/05/yyyy and see if we get a number bigger than 0 (indicating thatA1is 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 dateApril 5thof whatever yearA1is in. For example, ifA1isMay 2, 2012, then our formula would produceApril 5th, 2012. Simple enough.Now we just subtract the date we got from
A1and see if it's greater than 0 (which would mean thatA1came after April 5th).=A1-date(year(A1),4,5)accomplishes this nicely. We can throw this in an if statement like soAnd 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 (columnB, 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 cellB2to populate all the tax years without having to copy this formula a bunch of times.What I've done here is simply replace every instance of
A1withA2:Ato reference the entire column, and added=arrayformula()around the whole thing. Now, this is almost perfect, however if you have blank cells inA1at 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"".And that's it. You might be wondering why I only do
=if(A2:A)here instead of something likeif(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.