Change Calender starting month from January to April

[JamesCES]

Hi Guys,

The sheet shown below tracks spend by calendar years and we need to change it to spend by tax/fiscal years April to March. Does anyone know how to do this ?
Thanks

	Supplier 1
MONTH
Apr-17	£59.00
May-17	£60.00
Jun-17	£61.00
Jul-17	£62.00
Aug-17	£63.00
Sep-17	£64.00
Oct-17	£65.00
Nov-17	£66.00
Dec-17	£67.00
Jan-18	£68.00
Feb-18	£69.00
Mar-18	£70.00
Apr-18	£71.00
May-18	£72.00
Jun-18	£73.00
Jul-18	£74.00
Aug-18	£75.00
Sep-18	£76.00
Oct-18	£77.00
Nov-18	£78.00
Dec-18	£79.00
Jan-19	£80.00
Feb-19	£81.00
Mar-19	£81.00
Jan to Dec 2019	£ 242.00
Jan to Dec 2018	£ 882.00
Jan to Dec 2017	£ 567.00

<tbody>
</tbody>

The formula used for the current year is "=SUMPRODUCT((YEAR($A$3:$A$26)=YEAR(TODAY()))*B$3:B$26)"
The formula used for the previous year is "=SUMPRODUCT((YEAR($A$3:$A$26)=YEAR(TODAY())-1)*B$3:B$26)"
The formula used for 2 previous years is "=SUMPRODUCT((YEAR($A$3:$A$26)=YEAR(TODAY())-2)*B$3:B$26)"

Thanks

 

[steve the fish]

This year 1 april 2018 to 31 march 2019:

=SUMIFS(B:B,A:A,">="&DATE(YEAR(TODAY())-1,4,1),A:A,"<"&DATE(YEAR(TODAY()),4,1))

Previous year:

=SUMIFS(B:B,A:A,">="&DATE(YEAR(TODAY())-2,4,1),A:A,"<"&DATE(YEAR(TODAY())-1,4,1))

 

[Repush]

<date(yy,mm,dd))*b$3:b$26)<date(yy,mm,dd))*b$3:b$26)
<date_1)*b$3:b$26)-sumproduct(($a$3:$a$26<date_2)*b$3:b$26)<date_1)*b$3:b$26)-sumproduct(($a$3:$a$26<date_2)*b$3:b$26)< html=""></date_1)*b$3:b$26)-sumproduct(($a$3:$a$26<date_2)*b$3:b$26)<date_1)*b$3:b$26)-sumproduct(($a$3:$a$26<date_2)*b$3:b$26)<></date(yy,mm,dd))*b$3:b$26)<date(yy,mm,dd))*b$3:b$26)

 

[JamesCES]

Thanks Steve,

Works a Charm,

 

[JamesCES]

Hi Steve,

One more question if possible please, We also have a percentage change calculation on our spreadsheet which is also set to Jan to December, we would like to change this over to April to March also.
=((SUMPRODUCT((YEAR($B$13:$B$81)=YEAR(TODAY()))*(MONTH($B$13:$B$81)<=MONTH(TODAY()))*C$13:C$81))-(SUMPRODUCT((YEAR($B$13:$B$81)=YEAR(TODAY())-1)*(MONTH($B$13:$B$81)<=MONTH(TODAY()))*C$13:C$81)))/ABS(SUMPRODUCT((YEAR($B$13:$B$81)=YEAR(TODAY())-1)*(MONTH($B$13:$B$81)<=MONTH(TODAY()))*C$13:C$81))

Could you also convert this over please?
Thanks

 

[steve the fish]

So thats YTD-(YTD-1)/(YTD-1)?

 

[JamesCES]

Hi Steve,

I think I've posted the wrong formula there, we are trying to compare YTD to YTD at the same point last year.
So if we did £10 YTD this year and £5 YTD Lastyear we've had a 100% increase on last year.

Thanks

 

[steve the fish]

I actually think you posted the right one. Here goes:

=(SUMIFS(C13:C81,B13:B81,">="&DATE(YEAR(TODAY())-IF(MONTH(TODAY())>3,0,1),4,1),B13:B81,"<"&EOMONTH(TODAY(),0))-SUMIFS(C13:C81,B13:B81,">="&DATE(YEAR(TODAY())-1-IF(MONTH(TODAY())>3,0,1),4,1),B13:B81,"<"&EOMONTH(TODAY(),-12)))/SUMIFS(C13:C81,B13:B81,">="&DATE(YEAR(TODAY())-1-IF(MONTH(TODAY())>3,0,1),4,1),B13:B81,"<"&EOMONTH(TODAY(),-12))

Now all that said i think id use a lookup table and stamp each transaction to make the calcs easier:

Date	Fiscal Period	Fiscal Year
01/04/2017	1	2017
01/05/2017	2	2017
01/06/2017	3	2017
01/07/2017	4	2017
01/08/2017	5	2017
01/09/2017	6	2017
01/10/2017	7	2017
01/11/2017	8	2017
01/12/2017	9	2017
01/01/2018	10	2017
01/02/2018	11	2017
01/03/2018	12	2017
01/04/2018	1	2018
01/05/2018	2	2018
01/06/2018	3	2018
01/07/2018	4	2018
01/08/2018	5	2018
01/09/2018	6	2018
01/10/2018	7	2018
01/11/2018	8	2018
01/12/2018	9	2018
01/01/2019	10	2018
01/02/2019	11	2018
01/03/2019	12	2018
01/04/2019	1	2019
01/05/2019	2	2019
01/06/2019	3	2019

<colgroup><col><col><col></colgroup><tbody>
</tbody>

 

[JamesCES]

Thanks Steve,
Both problems now sorted out.
Thanks again.

 

[JamesCES]

This year 1 april 2018 to 31 march 2019:

=SUMIFS(B:B,A:A,">="&DATE(YEAR(TODAY())-1,4,1),A:A,"<"&DATE(YEAR(TODAY()),4,1))

Previous year:

=SUMIFS(B:B,A:A,">="&DATE(YEAR(TODAY())-2,4,1),A:A,"<"&DATE(YEAR(TODAY())-1,4,1))

Hi Steve,

Could you please advise how we update this formula to calculate the current tax year, April 19 to March 20, I've tried removing the -1 but the formula doesn't work.
Many thanks