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

			No. Of months Spread for each Financial Year End
Date Agreement	Agreement End Date	No. Of Months	31-03-2015	31-03-2016	31-03-2017	31-03-2018	31-03-2019	31-03-2020	31-03-2021	31-03-2022	Total
05-11-2016	05-07-2018	20	0	0	4	12	4	0	0	0	20
05-01-2017	05-01-2019	24	0	0	2	12	10	0	0	0	24
09-12-2016	09-12-2018	24	0	0	3	12	9	0	0	0	24
30-11-2016	30-11-2018	24	0	0	4	12	8	0	0	0	24
21-10-2016	21-10-2018	24	0	0	5	12	7	0	0	0	24
26-10-2016	26-10-2019	36	0	0	5	12	12	7	0	0	36
06-08-2016	06-08-2018	24	0	0	7	12	5	0	0	0	24
15-11-2016	15-11-2020	48	0	0	4	12	12	12	8	0	48
11-03-2017	11-03-2019	24	0	0	0	12	12	0	0	0	24
06-09-2016	06-03-2019	30	0	0	6	12	12	0	0	0	30

<colgroup><col span="2"><col><col span="8"><col></colgroup><tbody>
</tbody>

 

[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