Date Range (A) within another data range (B)

[Washc]

Given these date ranges (A)

04/29/2020 05/12/2020 - 0
05/13/2020 05/26/2020 - 1
05/27/2020 06/09/2020 - 1
06/10/2020 06/23/2020 - 1
06/24/2020 07/07/2020 - 1
07/08/2020 07/21/2020 - 1
07/22/2020 08/04/2020 - 0

How can I found out if this range 5/24/2020 - 7/14/2020 (B) falls inside the ones above...
in essence, I should get a 1 when it does, and a 0 when it does not...

Many thanks in advance.

 

[Eric W]

Is completely within the range, or overlaps the range? The following formula shows how many days overlap in the ranges. If the overlap is the total length of the first range (rows 4-6), then it's completely within.

Book1
	A	B	C	D	E	F	G
1	Start	End	Overlap			Start	End
2	4/29/2020	5/12/2020	0			5/24/2020	7/14/2020
3	5/13/2020	5/26/2020	3
4	5/27/2020	6/9/2020	14
5	6/10/2020	6/23/2020	14
6	6/24/2020	7/7/2020	14
7	7/8/2020	7/21/2020	7
8	7/22/2020	8/4/2020	0
9	7/22/2020	8/4/2020	0
Sheet8

Cell Formulas
Range	Formula
C2:C9	C2	=MAX(0,MIN(G$2,B2)-MAX(F$2,A2)+1)

 

[Washc]

thank you very much for the help.. if you don't mind, what does the G$2 do (in contrast to just G2)?

 

[Eric W]

The $ sign makes it an absolute reference, as opposed to a relative reference. The way Excel works is that you can type in the formula in the C2 cell, then you can copy the cell and paste it to C3 and the range references without a $ will change relative to where you copied the formula from, to where to copied it to. So B2 and A2 changed to B3 and A3. If you put the $ sign on it, it does not change so G$2 and F$2 stay on row 2.

 

[Washc]

that's cool!!! thank you.. I usually give ranges names to make it easier but this is pretty neat!!