Spreading Months between Financial Year

[Mahenderlalwani]

I Want to spread Agreement months between each Financial Year (31st March in India) , Sample Contract Given below - for example in the first cast Agreement Started in Nov 2016 , So for the financial year Mar 2016 to Mar 2017 we have 4 months and Same concept for all.

Someone please help me get this results via excel Formula

[TABLE="width: 1091"]
<colgroup><col span="2"><col><col span="8"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 8"]No. Of months Spread for each Financial Year End[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date Agreement[/TD]
[TD]Agreement End Date[/TD]
[TD]No. Of Months[/TD]
[TD]31-03-2015[/TD]
[TD]31-03-2016[/TD]
[TD]31-03-2017[/TD]
[TD]31-03-2018[/TD]
[TD]31-03-2019[/TD]
[TD]31-03-2020[/TD]
[TD]31-03-2021[/TD]
[TD]31-03-2022[/TD]
[TD]Total[/TD]
[/TR]
[TR]
[TD]05-11-2016[/TD]
[TD]05-07-2018[/TD]
[TD]20[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]12[/TD]
[TD]4[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]20[/TD]
[/TR]
[TR]
[TD]05-01-2017[/TD]
[TD]05-01-2019[/TD]
[TD]24[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]12[/TD]
[TD]10[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]09-12-2016[/TD]
[TD]09-12-2018[/TD]
[TD]24[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]3[/TD]
[TD]12[/TD]
[TD]9[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]30-11-2016[/TD]
[TD]30-11-2018[/TD]
[TD]24[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]12[/TD]
[TD]8[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]21-10-2016[/TD]
[TD]21-10-2018[/TD]
[TD]24[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]12[/TD]
[TD]7[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]26-10-2016[/TD]
[TD]26-10-2019[/TD]
[TD]36[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]7[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]36[/TD]
[/TR]
[TR]
[TD]06-08-2016[/TD]
[TD]06-08-2018[/TD]
[TD]24[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]7[/TD]
[TD]12[/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]15-11-2016[/TD]
[TD]15-11-2020[/TD]
[TD]48[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]4[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]8[/TD]
[TD]0[/TD]
[TD]48[/TD]
[/TR]
[TR]
[TD]11-03-2017[/TD]
[TD]11-03-2019[/TD]
[TD]24[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]06-09-2016[/TD]
[TD]06-03-2019[/TD]
[TD]30[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]6[/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]30[/TD]
[/TR]
</tbody>[/TABLE]

 

[jorismoerings]

Hi,

See if this helps:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Date Agreement	Agreement End Date	No. Of Months	31-3-2015	31-3-2016	31-3-2017	31-3-2018	31-3-2019	31-3-2020	31-3-2021	31-3-2022	Total
2	5-11-2016	5-7-2018	20	0	0	4	12	4	0	0	0	20
3	5-1-2017	5-1-2019	24	0	0	1	12	10	0	0	0	24
4	9-12-2016	9-12-2018	24	0	0	3	12	9	0	0	0	24
5	30-11-2016	30-11-2018	24	0	0	4	12	8	0	0	0	24
6	21-10-2016	21-10-2018	24	0	0	5	12	7	0	0	0	24
7	26-10-2016	26-10-2019	36	0	0	5	12	12	7	0	0	36
8	6-8-2016	6-8-2018	24	0	0	7	12	5	0	0	0	24
9	15-11-2016	15-11-2020	48	0	0	4	12	12	12	8	0	48
10	11-3-2017	11-3-2019	24	0	0	0	12	12	0	0	0	24
11	6-9-2016	6-3-2019	30	0	0	6	12	12	0	0	0	30
Sheet1

Cell Formulas
Range	Formula
D2	=INT(MOD(MAX(0,MIN(D$1,EOMONTH($B2,0)+1)-MAX(DATE(YEAR(D$1)-1,4,1),EOMONTH($A2,0))),366)/30)

 

[Mahenderlalwani]

The Cell F3 shows 1 whereas it should be 2 .
Rest and all is fine.

Thank you so very much.

I was using =DATEDIF(A2,$F1,"M") in Cell F2 along with if formula -
=IFERROR(IF(DATEDIF(G6,$AL$5,"M")>AA6,(DATEDIF(G6,$AL$5,"M")-AA6),IF((DATEDIF(G6,$AL$5,"M"))>12,12,(DATEDIF(G6,$AL$5,"M")))),0) but some issue with that.

 

[Mahenderlalwani]

Some issue with Jan month. Like in cell F3 it shows 1 where as it should be 2.
Rest all is perfect.
Thanks in advance.
Mahender Lalwani