Financial Year To Date & Percentage Spends Spreadsheet

JamesCES

New Member
Joined
Dec 22, 2016
Messages
15
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,

ABCD
Supplier 1Supplier 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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
893
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 22, 2016
Messages
15
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

New Member
Joined
Dec 22, 2016
Messages
15
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
 

Watch MrExcel Video

Forum statistics

Threads
1,127,613
Messages
5,625,848
Members
416,139
Latest member
MattBoard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top