Financial Year To Date & Percentage Spends Spreadsheet

[JamesCES]

Hi Guys,

Over the past few years this website has been very helpful in helping me build various formulas for this spreadsheet i have. 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.
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?
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)

Table below to show layout.
Thanks,

A	B	C	D
		Supplier 1	Supplier 2
	MONTH
	Apr-18
	May-18
	Jun-18
	Jul-18
	Aug-18
	Sep-18
	Oct-18
	Nov-18
	Dec-18
	Jan-19
	Feb-19
	Mar-19	£10.00
	Apr-19	£10.00
	May-19
	Jun-19
	Jul-19
	Aug-19
	Sep-19
	Oct-19
	Nov-19
	Dec-19
	Jan-20
	Feb-20
	Mar-20	£10.00	£10.00
	Apr-20	£10.00	£10.00
	May-20
	Jun-20
	Jul-20
	Aug-20	£30.00
	Sep-20
	Oct-20
	Nov-20
	Dec-20
	Jan-21
	Feb-21
	Mar-21

 

[CA_Punit]

Hello
So you question more or less requires alteration in YEAR(TODAY())-1, This gives 2019 and YEAR(TODAY()) gives 2020
so changing YEAR(TODAY())-1 to YEAR(TODAY()) will give 2020

and for March part change this to YEAR(TODAY()) to YEAR(TODAY()) +1

Will the same formula work in January to March next year?

Is it January to march or april to march?? Please clarify

Would the formula change much to do the previous financial year?

YEAR(TODAY()) gives you current year so YEAR(TODAY())-1 gives you previous 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)

Yes it is possible, =SUMIFS(C:C,$B:$B,">="&DATE(YEAR(TODAY()),4,1),$B:$B,"<"&MIN(DATE(YEAR(TODAY())+1,4,1),DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))))
Will give you min of 07th August 2020 or 31st March 2021

 

[JamesCES]

Hi,
Thanks for your reply, a couple of things to clarify.
1) Our Tax/Trading year is April 1st to the 31st March. I've had working formula's before that are fine but after the new year break the formula's stop working because of the +1 or -1 year issue.

a) Is there a way excel can check to see which trading year is current?​

b) What would be the formula i would need to place to do this?​

2) The percentage change formula can i just reconfirm the requirements of the formula, We would like to see a positive or negative change in the figure of spend from the current year to previous year.

a) if we load the spreadsheet on 16/08/2020 it will compare the spending of (April 2020 to August 2020) with the spending of (April 2019 to August 2019)​

b)if we load the spreadsheet on 05/02/2021 it will compare the spending of (April 2020 to February 2021) with the spending of (April 2019 to February 2020)​

Thanks

 

[JamesCES]

Hello
So you question more or less requires alteration in YEAR(TODAY())-1, This gives 2019 and YEAR(TODAY()) gives 2020
so changing YEAR(TODAY())-1 to YEAR(TODAY()) will give 2020

and for March part change this to YEAR(TODAY()) to YEAR(TODAY()) +1




Is it January to march or april to march?? Please clarify



YEAR(TODAY()) gives you current year so YEAR(TODAY())-1 gives you previous year



Yes it is possible, =SUMIFS(C:C,$B:$B,">="&DATE(YEAR(TODAY()),4,1),$B:$B,"<"&MIN(DATE(YEAR(TODAY())+1,4,1),DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))))
Will give you min of 07th August 2020 or 31st March 2021

Hi, Not sure if you saw my reply with a couple of questions?

Hi,
Thanks for your reply, a couple of things to clarify.
1) Our Tax/Trading year is April 1st to the 31st March. I've had working formula's before that are fine but after the new year break the formula's stop working because of the +1 or -1 year issue.

a) Is there a way excel can check to see which trading year is current?
b) What would be the formula i would need to place to do this?
2) The percentage change formula can i just reconfirm the requirements of the formula, We would like to see a positive or negative change in the figure of spend from the current year to previous year.

a) if we load the spreadsheet on 16/08/2020 it will compare the spending of (April 2020 to August 2020) with the spending of (April 2019 to August 2019)
b)if we load the spreadsheet on 05/02/2021 it will compare the spending of (April 2020 to February 2021) with the spending of (April 2019 to February 2020)

Thanks