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

<tbody>
[TD="class: xl65, width: 136"]
[/TD]
[TD="class: xl66, width: 120"]Supplier 1
[/TD]

[TD="class: xl67"]MONTH
[/TD]
[TD="class: xl68"] [/TD]

[TD="class: xl69"]Apr-17
[/TD]
[TD="class: xl70"]£59.00[/TD]

[TD="class: xl69"]May-17[/TD]
[TD="class: xl72"]£60.00[/TD]

[TD="class: xl69"]Jun-17[/TD]
[TD="class: xl72"]£61.00[/TD]

[TD="class: xl69"]Jul-17[/TD]
[TD="class: xl72"]£62.00[/TD]

[TD="class: xl69"]Aug-17[/TD]
[TD="class: xl70"]£63.00[/TD]

[TD="class: xl69"]Sep-17[/TD]
[TD="class: xl72"]£64.00[/TD]

[TD="class: xl69"]Oct-17
[/TD]
[TD="class: xl72"]£65.00[/TD]

[TD="class: xl69"]Nov-17
[/TD]
[TD="class: xl72"]£66.00[/TD]

[TD="class: xl69"]Dec-17[/TD]
[TD="class: xl70"]£67.00[/TD]

[TD="class: xl69"]Jan-18[/TD]
[TD="class: xl72"]£68.00[/TD]

[TD="class: xl69"]Feb-18[/TD]
[TD="class: xl72"]£69.00[/TD]

[TD="class: xl69"]Mar-18[/TD]
[TD="class: xl72"]£70.00[/TD]

[TD="class: xl69"]Apr-18[/TD]
[TD="class: xl70"]£71.00[/TD]

[TD="class: xl69"]May-18
[/TD]
[TD="class: xl72"]£72.00[/TD]

[TD="class: xl69"]Jun-18
[/TD]
[TD="class: xl72"]£73.00[/TD]

[TD="class: xl69"]Jul-18[/TD]
[TD="class: xl72"]£74.00[/TD]

[TD="class: xl69"]Aug-18[/TD]
[TD="class: xl70"]£75.00[/TD]

[TD="class: xl69"]Sep-18[/TD]
[TD="class: xl72"]£76.00[/TD]

[TD="class: xl69"]Oct-18[/TD]
[TD="class: xl72"]£77.00[/TD]

[TD="class: xl69"]Nov-18
[/TD]
[TD="class: xl72"]£78.00[/TD]

[TD="class: xl69"]Dec-18
[/TD]
[TD="class: xl70"]£79.00[/TD]

[TD="class: xl69"]Jan-19[/TD]
[TD="class: xl72"]£80.00[/TD]

[TD="class: xl69"]Feb-19[/TD]
[TD="class: xl72"]£81.00[/TD]

[TD="class: xl69"]Mar-19[/TD]
[TD="class: xl72"]£81.00
[/TD]

[TD="class: xl74"]Jan to Dec 2019[/TD]
[TD="class: xl75"] £ 242.00 [/TD]

[TD="class: xl74"]Jan to Dec 2018
[/TD]
[TD="class: xl75"] £ 882.00
[/TD]

[TD="class: xl74"]Jan to Dec 2017
[/TD]
[TD="class: xl75"] £ 567.00 [/TD]

</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:

[TABLE="width: 231"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Fiscal Period
[/TD]
[TD]Fiscal Year
[/TD]
[/TR]
[TR]
[TD="align: right"]01/04/2017[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2017[/TD]
[/TR]
[TR]
[TD="align: right"]01/05/2017[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2017[/TD]
[/TR]
[TR]
[TD="align: right"]01/06/2017[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2017[/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2017[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2017[/TD]
[/TR]
[TR]
[TD="align: right"]01/08/2017[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2017[/TD]
[/TR]
[TR]
[TD="align: right"]01/09/2017[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2017[/TD]
[/TR]
[TR]
[TD="align: right"]01/10/2017[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2017[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2017[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2017[/TD]
[/TR]
[TR]
[TD="align: right"]01/12/2017[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2017[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2018[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2017[/TD]
[/TR]
[TR]
[TD="align: right"]01/02/2018[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]2017[/TD]
[/TR]
[TR]
[TD="align: right"]01/03/2018[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]2017[/TD]
[/TR]
[TR]
[TD="align: right"]01/04/2018[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="align: right"]01/05/2018[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="align: right"]01/06/2018[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="align: right"]01/07/2018[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="align: right"]01/08/2018[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="align: right"]01/09/2018[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="align: right"]01/10/2018[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="align: right"]01/11/2018[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="align: right"]01/12/2018[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="align: right"]01/01/2019[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="align: right"]01/02/2019[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="align: right"]01/03/2019[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]2018[/TD]
[/TR]
[TR]
[TD="align: right"]01/04/2019[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD="align: right"]01/05/2019[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2019[/TD]
[/TR]
[TR]
[TD="align: right"]01/06/2019[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2019[/TD]
[/TR]
</tbody>[/TABLE]

 

[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