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:
<tbody>
</tbody>
INPUT: Calendar Years:
<tbody>
</tbody>
DESIRED OUTPUT: Calculation to Allocate Months in Contract for prorating revenue:
<tbody>
</tbody>
I have been struggling to figure out a straight-way method of doing this in a single cell.
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:
Start | End | Total Value | Total Months |
9/27/2017 | 9/28/2020 | $39,600 | 36.03 |
<tbody>
</tbody>
INPUT: Calendar Years:
Year | Start | End |
2016 | 1/1/2016 | 12/31/2016 |
2017 | 1/1/2017 | 12/31/2017 |
2018 | 1/1/2018 | 12/31/2018 |
2019 | 1/1/2019 | 12/31/2019 |
2020 | 1/1/2020 | 12/31/2020 |
2021 | 1/1/2021 | 12/31/2021 |
<tbody>
</tbody>
DESIRED OUTPUT: Calculation to Allocate Months in Contract for prorating revenue:
Year | 2017 | 2018 | 2019 | 2020 | Total |
Months | 3.09 | 12.00 | 12.00 | 8.94 | 36.03 |
Revenue | $3,396 | $13,188 | $13,188 | $9,828 | $39,600 |
<tbody>
</tbody>
I have been struggling to figure out a straight-way method of doing this in a single cell.