Number of months in live contract between start/end dates

choubix

Board Regular
Joined
Jul 3, 2008
Messages
85
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
Joined
Dec 30, 2008
Messages
14,617
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 3, 2008
Messages
85
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?
Hi! thanks for your message :)
I am uploading an image to illustrate.

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

datesExcel.PNG
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,617
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 3, 2008
Messages
85

ADVERTISEMENT

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
Joined
Feb 15, 2002
Messages
5,063
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Jul 3, 2008
Messages
85

ADVERTISEMENT

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
Joined
Feb 15, 2002
Messages
5,063
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
"how median of 3 dates work"

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,617
Office Version
  1. 365
Platform
  1. Windows
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")
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top