Calculate # of Months in a Calendar Yr Given a Start/End Date Range

[Redfly88]

Hi, can someone please help me derive a formula to calculate the number of months that would fall within any given Calendar Year based on a contracts start and end date range. For instance, if I had a contract start date of 9/29/2017 and contract end date of 9/28/2020, and I know each calendar year is from Jan 1st to Dec 31st, then how could I calculate the number of months in each calendar year. I want to be able to take the pro-rated months the contract falls in each year and multiply that to get the total pro-rated revenue each calendar year.

So conceptually like this: (Total months contract exists in a Calendar Year/Total months for the entire contract) X Total Contract Revenue.

INPUT: Given Contract:
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Start[/TD]
[TD="align: center"]End[/TD]
[TD="align: center"]Total Value[/TD]
[TD="align: center"]Total Months[/TD]
[/TR]
[TR]
[TD="align: center"]9/27/2017[/TD]
[TD="align: center"]9/28/2020[/TD]
[TD="align: center"]$39,600[/TD]
[TD="align: center"]36.03[/TD]
[/TR]
</tbody>[/TABLE]

INPUT: Calendar Years:
[TABLE="width: 300"]
<tbody>[TR]
[TD="align: center"]Year[/TD]
[TD="align: center"]Start[/TD]
[TD="align: center"]End[/TD]
[/TR]
[TR]
[TD="align: center"]2016[/TD]
[TD="align: center"]1/1/2016[/TD]
[TD="align: center"]12/31/2016[/TD]
[/TR]
[TR]
[TD="align: center"]2017[/TD]
[TD="align: center"]1/1/2017[/TD]
[TD="align: center"]12/31/2017[/TD]
[/TR]
[TR]
[TD="align: center"]2018[/TD]
[TD="align: center"]1/1/2018[/TD]
[TD="align: center"]12/31/2018[/TD]
[/TR]
[TR]
[TD="align: center"]2019[/TD]
[TD="align: center"]1/1/2019[/TD]
[TD="align: center"]12/31/2019
[/TD]
[/TR]
[TR]
[TD="align: center"]2020
[/TD]
[TD="align: center"]1/1/2020[/TD]
[TD="align: center"]12/31/2020[/TD]
[/TR]
[TR]
[TD="align: center"]2021[/TD]
[TD="align: center"]1/1/2021[/TD]
[TD="align: center"]12/31/2021[/TD]
[/TR]
</tbody>[/TABLE]


DESIRED OUTPUT: Calculation to Allocate Months in Contract for prorating revenue:
[TABLE="width: 300"]
<tbody>[TR]
[TD="align: center"]Year[/TD]
[TD="align: center"]2017[/TD]
[TD="align: center"]2018[/TD]
[TD="align: center"]2019[/TD]
[TD="align: center"]2020[/TD]
[TD="align: center"]Total[/TD]
[/TR]
[TR]
[TD="align: center"]Months[/TD]
[TD="align: center"]3.09[/TD]
[TD="align: center"]12.00[/TD]
[TD="align: center"]12.00[/TD]
[TD="align: center"]8.94[/TD]
[TD="align: center"]36.03[/TD]
[/TR]
[TR]
[TD="align: center"]Revenue[/TD]
[TD="align: center"]$3,396[/TD]
[TD="align: center"]$13,188[/TD]
[TD="align: center"]$13,188[/TD]
[TD="align: center"]$9,828[/TD]
[TD]$39,600[/TD]
[/TR]
</tbody>[/TABLE]

I have been struggling to figure out a straight-way method of doing this in a single cell.

 

[DRSteele]

I found it expedient to use days instead of months; it's hard to rationalise what 36.03 months means because the twelve months have different days and there are leap years.

I hope this helps. I included dates outside the range within the contract to tests the formulas.

Copy B6 across and down.

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	A	B	C	D	E	F	G	H
1	Start	End	Total Value	Total Days
2	9/27/17	9/28/20	$39,600	1098
3
4
5	Year	2016	2017	2018	2019	2020	2021	Total
6	Days	0.00	96.00	365.00	365.00	272.00	0.00	1098
7	Revenue	0.00	3,462.30	13,163.93	13,163.93	9,809.84	0.00	39,600.00
Sheet41

Cell Formulas
Range	Formula
D2	=B2-A2+1
B6	=IF((B5>=YEAR($A$2))*(B5<=YEAR($B$2)),DATE(B5,12,31)-MAX(DATE(B5,1,1),$A$2)+1,0)
H6	=SUM(B6:G6)
H7	=SUM(B7:G7)

[/FONT]

 

[Redfly88]

Thanks, that is better than what I had which was nothing. But for some reason, I am not getting the same amount total days you do as I get 1096 days using the same formula.

Also, my Days for the Calendar years differ even though I use the same formula. I get 94, 365, 365, 366 days respectively for Calendar Years 2017, 2018, 2019, 2020. For some reason 2020 is giving me 366 days. Not sure what I am doing wrong.

I am not sure why since it seems to be working the other Calendar Years. Formula makes sense as you're saying if the Calendar Year falls in between the contract start and end date then take the last day of that specific Calendar Year and subtract the maximum of the two number either the first of the Calendar Year or the contract date).

 

[Redfly88]

Here are the values I am getting. For some reason, the last year 2020 keeps showing 366 days even though the other years are correct instead of 272 days.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]2017[/TD]
[TD="align: center"]2018[/TD]
[TD="align: center"]2019[/TD]
[TD="align: center"]2020[/TD]
[TD="align: center"]Total
[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]96[/TD]
[TD="align: center"]365[/TD]
[TD="align: center"]365[/TD]
[TD="align: center"]366[/TD]
[TD="align: center"]1192[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]$3,462.30[/TD]
[TD="align: center"]$13,163.93[/TD]
[TD="align: center"]$13,163.93[/TD]
[TD="align: center"]$13,200.00[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]