Supplier Spending Spreadsheet with Financial Year To Date (YTD)

[james-ces]

Hi Guys,

Hope you can help with the below,
I've got a few problems with a couple of my formula's and didn't know if anyone could point out the fault or suggest a better way to show the information.

This is the formula i have that was supposed to do my Tax Year To Date =SUMIFS(C:C,$B:$B,">="&DATE(YEAR(TODAY())-1,4,1),$B:$B,"<"&DATE(YEAR(TODAY()),4,1))
1) When i run it today it shows figures from April 2019 to March 2020, but doesn't show me April 2020 to March 2021
2) How do i alter it to show April 2020 to March 2021?
3) Will the same formula work in January to March next year, as with some of these formula's in the past work fine April to December but get to January to March and they don't work correctly.
3) Would the formula change much to do the previous financial year?
4) Is it possible to workout the percentage change between the current Tax Year to the previous Tax Year at the same point, ( So if the formula is run on 16th September 2020 the formula would compare 1st April upto 16th September 2020 to 1st April upto 16th September 2019)

Spreadsheet attached for information
Thanks,

[TMS]

If you take the year from today's date, that is, 20, and take 1 from it, you get 19. So, you’re starting with 1 April 2019. Similarly, you end with 1 April 2020. Note, that's not actually the Tax Year. To get 20 to 21, don't subtract 1 from the start year and ADD 1 to the end year.

[james-ces]

Hi Trevor,

Thanks for the reply, but with our Tax/Financial year going April to March, these formulas work OK April to December but come January to March they stop because we are in a different year. Is there a way this can be corrected? Could this be put inside an IF statement? If in months 4-12 its the start year, if in months 1-3 subtract -1 from the start year?
thanks

James

[TMS]

I'm sort of assuming you're in Hinckley, UK ...

https://help.gosimpletax.com/hc/en-g...ing-Deadlines-

The tax year runs from the 6th April - 5th April. Your accounts need to be calculated according to these dates. You can start submitting your tax return on the first day of the next tax year 6th April.

[james-ces]

Hi,

Thanks for the link but we are not using it to submit tax returns etc, we are using the spreadsheet to track how much we spend with certain suppliers. The formula is just to calculate it during our trading year not Jan to December.
Is this something you could assist with?
Thanks

[TMS]

Personally, I don't think I’d use TODAY() to get the current year as clearly it will not always reflect the correct Tax Year. I'd probably put the current Tax Year start (either year or full date) in a cell and calculate all the other start and end dates from that.

So, if you want to calculate your current tax return, I’d put 2020 in a cell, say cell A1, and calculate the start year as A1-1. You can then use that to determine previous tax year start and end dates.