Date Overlap

[Julien404]

Hello,
I'm trying to find a "simple" formula to determine whether a set of dates belongs to (or overlaps with) specific dates.

So in this example, the sets C, D, E and F would be given as a result because they overlap with the range 5 to 8.
The only way i've found to do this so far is combining several criteria in a filter function (is the beginning date between 5 and 8 OR is the en date betwenn 5 and 8 OR is the beginning date before 5 AND the end date after 8).

It seems clunky and i figured i could go about it the opposite way, as in filter out the values that have an beggining date after 8 or an end date before 5. But i can't figure it out.

Thanks for the help

 

[jasonb75]

This will do it

Book1
	A	B	C
2	A	9	11
3	B	1	4
4	C	6	7
5	D	6	11
6	E	3	6
7	F	2	10
8
9	Crit	5	8
10
11		C
12		D
13		E
14		F
Sheet1

Cell Formulas
Range	Formula
B11:B14	B11	=FILTER(A2:A7,(C2:C7>B9)*(B2:B7<C9))
Dynamic array formulas.

 

[Julien404]

Thanks for the help, can't beleive i didn't think of that