Number of months in live contract between start/end dates

[choubix]

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

 

[jasonb75]

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]

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

 

[jasonb75]

I don't have time to set up a test sheet right now, from what I can remember doing similar before.

=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]

I don't have time to set up a test sheet right now, from what I can remember doing similar before.

=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:

=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]

2 alternatives that you can consider

T202104a.xlsm
	A	B	C	D	E	F	G	H	I	J
2		Start	End	Months			2018	2019	2020	2021
3		01-Apr-19	31-Mar-20	12			0	9	3	0
4		01-Dec-20	30-Nov-21	12			0	0	1	11
6cc

Cell Formulas
Range	Formula
G3:J4	G3	=ROUND(MAX(0, MIN(G$2, $C3) - MAX(EDATE(G$2,-12), $B3-1))/365*$D3,0)
D3:D4	D3	=DATEDIF(B3-1,C3,"m")

T202104a.xlsm
	A	B	C	D	E	F	G	H
11		Start	End	Months	2018	2019	2020	2021
12		01-Apr-19	31-Mar-20	12	0	9	3	0
13		01-Dec-20	30-Nov-21	12	0	0	1	11
6cc

Cell Formulas
Range	Formula
D12:D13	D12	=DATEDIF(B12-1,C12,"m")
E12:H13	E12	=DATEDIF(MEDIAN($B12,$C12+1,DATE(E$11,1,1)),MEDIAN($B12,$C12+1,DATE(E$11,12,31)+1),"M")

 

[choubix]

2 alternatives that you can consider

T202104a.xlsm
	A	B	C	D	E	F	G	H	I	J
2		Start	End	Months			2018	2019	2020	2021
3		01-Apr-19	31-Mar-20	12			0	9	3	0
4		01-Dec-20	30-Nov-21	12			0	0	1	11
6cc

Cell Formulas
Range	Formula
G3:J4	G3	=ROUND(MAX(0, MIN(G$2, $C3) - MAX(EDATE(G$2,-12), $B3-1))/365*$D3,0)
D3:D4	D3	=DATEDIF(B3-1,C3,"m")

T202104a.xlsm
	A	B	C	D	E	F	G	H
11		Start	End	Months	2018	2019	2020	2021
12		01-Apr-19	31-Mar-20	12	0	9	3	0
13		01-Dec-20	30-Nov-21	12	0	0	1	11
6cc

Cell Formulas
Range	Formula
D12:D13	D12	=DATEDIF(B12-1,C12,"m")
E12:H13	E12	=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]

"how median of 3 dates work"

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

 

[jasonb75]

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
	B	C	D	E	F	G	H
1	contract start	contract end	year start	year end	1st median	2nd median	Months
2	04/01/19	03/31/21	01/01/18	12/31/18	04/01/19	04/01/19	0
3			01/01/19	12/31/19	04/01/19	12/31/19	9
4			01/01/20	12/31/20	01/01/20	12/31/20	12
5			01/01/21	12/31/21	01/01/21	03/31/21	3
6			01/01/22	12/31/22	03/31/21	03/31/21	0
Sheet12

Cell Formulas
Range	Formula
F2:G6	F2	=MEDIAN($B$2,$C$2,D2)
H2:H6	H2	=DATEDIF(F2,G2+1,"M")