Data Range Formula to Calculate Number of Months in Current Quarter

[mcmuney]

Hi Everyone, I'm having a brain fart. Can anyone help me with this? It will be greatly appreciated.

My data has these two date columns and I need to figure out how many months fall in Q2 of 2019. The 3rd column shows the desired results it should give me using the 6/30/2019 date (For Q1, I'd use 3/31/19, Q3 9/30/19, etc.


[TABLE="width: 218"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Sched Date[/TD]
[TD]Termination[/TD]
[TD]Q2 Result[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2019[/TD]
[TD="align: right"]2/28/2019[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD="align: right"]1/1/2019[/TD]
[TD="align: right"]2/28/2021[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]2/1/2019[/TD]
[TD="align: right"]2/28/2021[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]3/1/2019[/TD]
[TD="align: right"]2/28/2021[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]4/1/2019[/TD]
[TD="align: right"]2/28/2021[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/2019[/TD]
[TD="align: right"]2/28/2021[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]6/1/2019[/TD]
[TD="align: right"]10/31/2019[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Month[/TD]
[TD="align: right"]6/30/2019[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Thanks.

 

[Eric W]

The formula in G2 is the classic formula to find out how many overlapping days there are in 2 ranges. Then you can add the formula in H2 to get it to months.

Book1
	A	B	C	D	E	F	G	H
1	Sched Date	Termination	Q2 Result		Date 1	Date 2	Days	Months
2	1/1/2019	2/28/2019	0		4/1/2019	6/30/2019	0	0
3	1/1/2019	2/28/2021	3				90	3
4	2/1/2019	2/28/2021	3				90	3
5	3/1/2019	2/28/2021	3				90	3
6	4/1/2019	2/28/2021	3				90	3
7	5/1/2019	2/28/2021	2				60	2
8	6/1/2019	10/31/2019	1				29	1
9	Month	6/30/2019
Sheet8

Cell Formulas
Range	Formula
G2	=MAX(0,MIN($F$2,B2)-MAX($E$2,A2))
H2	=ROUND(G2/30,1)