Month counting formula between 2 dates, including months per fiscal year

[Bootharoo]

Hi,

We take on contracts typically ranging from 3 months to 36 months. we always use the 1st of the month for the start and end dates.

for example:

Contract 1: 1/4/18-1/12/18 = duration 8 months
Contract 2: 1/2/18 - 1/2/18 = duration 12 months
Contract 3: 1/1/18 - 1/1/20 = duration 24 months

I have put the DATEIF formula in place to count the number of months between 2 dates.

The hard bit is counting the number of months in each financial year. Our year end is 1st April for the benefit of the spreadsheet. So in the context of the above contracts:

Contract 1: all 8 months are in Fiscal year ending 1/4/2019
Contract 2: 2 months are in Fiscal year ending 1/4/2018 and the remaining 10 months are in Fiscal year ending 1/4/2019
Contract 3: 3 months are in fiscal year ending 1/4/2018, 12 months are in fiscal year ending 1/4/2019 and the remaining 9 months are in fiscal year ending 1/4/2020

If anyone can provide me with a decent formula for the above I would appreciate it

Thanks

Joe!

 

[StephenCrump]

Welcome to the Forum!

D3: =IFERROR(DATEDIF(MAX($B3,EDATE(D$2,-12)),MIN($C3,D$2),"m"),0)

Book1
	A	B	C	D	E	F	G
1				Year end	------>
2	Contract #	Start	End	1 Apr 2018	1 Apr 2019	1 Apr 2020	1 Apr 2021
3	1	1 Apr 2018	1 Dec 2018	0	8	0	0
4	2	1 Feb 2018	1 Feb 2019	2	10	0	0
5	3	1 Jan 2018	1 Jan 2020	3	12	9	0
Sheet1

 

[Bootharoo]

Welcome to the Forum!

D3: =IFERROR(DATEDIF(MAX($B3,EDATE(D$2,-12)),MIN($C3,D$2),"m"),0)

	A	B	C	D	E	F	G
------>

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Year end[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Contract #[/TD]
[TD="align: center"]Start[/TD]
[TD="align: center"]End[/TD]
[TD="align: right"]1 Apr 2018[/TD]
[TD="align: right"]1 Apr 2019[/TD]
[TD="align: right"]1 Apr 2020[/TD]
[TD="align: right"]1 Apr 2021[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1 Apr 2018[/TD]
[TD="align: center"]1 Dec 2018[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1 Feb 2018[/TD]
[TD="align: center"]1 Feb 2019[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1 Jan 2018[/TD]
[TD="align: center"]1 Jan 2020[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]

</tbody>

ABSOLUTE LEGEND!! I can't tell you how much time this will save me! Thanks