# Financial Year To Date & Percentage Spends Spreadsheet

#### JamesCES

##### New Member
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

### 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
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
Hi,
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
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,
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

Replies
12
Views
162
Replies
5
Views
64
Replies
10
Views
218
Replies
2
Views
132
Replies
10
Views
267

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.

### Which adblocker are you using?

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

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