# Number of months in live contract between start/end dates

#### choubix

##### Board Regular
Hi guys,

it's been ages since I had to post a question on Excel.
I hope a good Samaritan will be able to assist

Basically I have contracts with a starting date and an end date.
These contract scan be for 1, 3, 6 12, 24, 36 months

A1 : 6/1/2019
A2 : 12/31/2021

A3 : 30 (months)

I would need in Cell A5, A6, A7 to have the number of months of live contracts calculated (ie: A5 : 6, A6 : 12, A7: 12)

Has anyone an idea on how to create a formula for this please?

Thank you

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### jasonb75

##### Well-known Member
How are you calculating your results? It is not 100% clear from the example provided. The wording of the question says to 'count live contracts' indicating multiple contracts over the same dates, yet your example only shows a single pair of dates.

I was looking at it possibly being the number of months per year for a single contract, but if that was correct then shouldn't the first result be 7 instead of 6?

#### choubix

##### Board Regular
How are you calculating your results? It is not 100% clear from the example provided. The wording of the question says to 'count live contracts' indicating multiple contracts over the same dates, yet your example only shows a single pair of dates.

I was looking at it possibly being the number of months per year for a single contract, but if that was correct then shouldn't the first result be 7 instead of 6?

Below: customer 1 has a contract from 4/1/2019 to 3/31/2020 so it would have 0 active months in 2018, 9 months in 2019 and 3 in 2020

#### jasonb75

##### Well-known Member
I don't have time to set up a test sheet right now, from what I can remember doing similar before.
Excel Formula:
``=DATEDIF(MEDIAN(\$B2,\$C2,DATE(E\$1,1,1)),MEDIAN(\$B2,\$C2,DATE(E\$1,12,31)),"M")``
I've assumed that "Customer" is in A1, with no merged rows or columns in the sheet.

I'm going to be offline for about the next 7 hours, will check when I return to see if it worked and update as needed.

#### choubix

##### Board Regular

I don't have time to set up a test sheet right now, from what I can remember doing similar before.
Excel Formula:
``=DATEDIF(MEDIAN(\$B2,\$C2,DATE(E\$1,1,1)),MEDIAN(\$B2,\$C2,DATE(E\$1,12,31)),"M")``
I've assumed that "Customer" is in A1, with no merged rows or columns in the sheet.

I'm going to be offline for about the next 7 hours, will check when I return to see if it worked and update as needed.

Thanks @jasonb75!

I would not have thought of this approach (I dont remember using Median for dates ever)
There was a single adjustment I had to make: a "+1" for the second part of the formula as below:

Excel Formula:
``=DATEDIF(MEDIAN(\$B2,\$C2,DATE(E\$1,1,1)),MEDIAN(\$B2,\$C2,DATE(E\$1,12,31))+1,"M")``

How does "median" work here?

I understand that if I have a contract starting in mid 2019 and running until 2021, the formula is taking mid 2019 to end of 2019 and returning the number of months but once things move to the next year, it looks like voodoo to me...

Thanks again

#### Dave Patton

##### Well-known Member
2 alternatives that you can consider

T202104a.xlsm
ABCDEFGHIJ
2StartEndMonths2018201920202021
301-Apr-1931-Mar-20120930
401-Dec-2030-Nov-211200111
6cc
Cell Formulas
RangeFormula
G3:J4G3=ROUND(MAX(0, MIN(G\$2, \$C3) - MAX(EDATE(G\$2,-12), \$B3-1))/365*\$D3,0)
D3:D4D3=DATEDIF(B3-1,C3,"m")

T202104a.xlsm
ABCDEFGH
11StartEndMonths2018201920202021
1201-Apr-1931-Mar-20120930
1301-Dec-2030-Nov-211200111
6cc
Cell Formulas
RangeFormula
D12:D13D12=DATEDIF(B12-1,C12,"m")
E12:H13E12=DATEDIF(MEDIAN(\$B12,\$C12+1,DATE(E\$11,1,1)),MEDIAN(\$B12,\$C12+1,DATE(E\$11,12,31)+1),"M")

#### choubix

##### Board Regular

2 alternatives that you can consider

T202104a.xlsm
ABCDEFGHIJ
2StartEndMonths2018201920202021
301-Apr-1931-Mar-20120930
401-Dec-2030-Nov-211200111
6cc
Cell Formulas
RangeFormula
G3:J4G3=ROUND(MAX(0, MIN(G\$2, \$C3) - MAX(EDATE(G\$2,-12), \$B3-1))/365*\$D3,0)
D3:D4D3=DATEDIF(B3-1,C3,"m")

T202104a.xlsm
ABCDEFGH
11StartEndMonths2018201920202021
1201-Apr-1931-Mar-20120930
1301-Dec-2030-Nov-211200111
6cc
Cell Formulas
RangeFormula
D12:D13D12=DATEDIF(B12-1,C12,"m")
E12:H13E12=DATEDIF(MEDIAN(\$B12,\$C12+1,DATE(E\$11,1,1)),MEDIAN(\$B12,\$C12+1,DATE(E\$11,12,31)+1),"M")
Thanks Dave!
Is there a write up somewhere on how median of 3 dates works by any chance please? It seems counterintuitive to me (but it works )

Really appreciate the assist with here

#### Dave Patton

##### Well-known Member
"how median of 3 dates work"

Try stepping through the formula with Excel's Formulas Formula Evaluate.

#### jasonb75

##### Well-known Member
Try stepping through the formula with Excel's Formulas Formula Evaluate.
I'm going to second Dave's suggestion to use evaluation, the way that this works is easier to understand than to explain. The mini sheet below may make it a bit easier to see how the dates line up for the median function to extract the middle one.

autoclose test.xlsm
BCDEFGH
1contract startcontract endyear startyear end1st median2nd medianMonths
204/01/1903/31/2101/01/1812/31/1804/01/1904/01/190
301/01/1912/31/1904/01/1912/31/199
401/01/2012/31/2001/01/2012/31/2012
501/01/2112/31/2101/01/2103/31/213
601/01/2212/31/2203/31/2103/31/210
Sheet12
Cell Formulas
RangeFormula
F2:G6F2=MEDIAN(\$B\$2,\$C\$2,D2)
H2:H6H2=DATEDIF(F2,G2+1,"M")

Replies
6
Views
156
Replies
12
Views
157
Replies
2
Views
186
Replies
4
Views
139
Replies
0
Views
365

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,111
Messages
5,768,163
Members
425,458
Latest member
Jaspal1996

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back