Count Overlapping Time Ranges per Hour

[tbush33]

Hello,

I am trying to count how many time ranges fall within an hour (i.e. 13:00 - 13:59, 14:00 - 14:59). It would be two time ranges. Here is an example:

Monday Time Ranges			Hours of Operation		Count of Monday Time Ranges
7:29	8:54		5:00	5:59	0
7:55	8:32		6:00	6:59	0
7:58	8:59		7:00	7:59	4
7:59	9:30		8:00	8:59	7
8:01	9:05		9:00	9:59	4
8:04	8:54		10:00	10:59	0
8:20	9:45		11:00	11:59	0

The Count would be how many times the "Monday Time Ranges" overlap the "Hours of Operation".

Any idea how I would do this?

 

[shknbk2]

Here's my take: a really long array formula. (note that your count of the 9:00 range should be 3, not 4)

Assuming Monday time ranges are in A2:A8, putting this formula in F2 (the first Count cell) and pressing Shift+Ctrl+Enter will put the array formula in that cell. Double click lower right part of cell or drag down to fill.

=COUNTIFS($A$2:$A$8,">="&D2,$A$2:$A$8,"<="&E2+TIME(0,1,0),$B$2:$B$8,">="&D2,$B$2:$B$8,"<="&E2+TIME(0,1,0))+COUNTIFS($A$2:$A$8,"<="&D2,$A$2:$A$8,"<="&E2+TIME(0,1,0),$B$2:$B$8,">="&D2,$B$2:$B$8,"<="&E2+TIME(0,1,0))+COUNTIFS($A$2:$A$8,">="&D2,$A$2:$A$8,"<="&E2+TIME(0,1,0),$B$2:$B$8,">="&D2,$B$2:$B$8,">="&E2+TIME(0,1,0))+COUNTIFS($A$2:$A$8,"<="&D2,$A$2:$A$8,"<="&E2+TIME(0,1,0),$B$2:$B$8,">="&D2,$B$2:$B$8,">="&E2+TIME(0,1,0))

There are 4 CountIfS calculations:
1: Find all ranges between hours of operation
2: Find all ranges starting before hours of operation but ending within hours of operation
3: Find all ranges starting within hours of operation but ending after hours of operation
4: Find all ranges starting before hours of operation and ending after hours of operation.

For some reason, I had to add 1 minute to the ending hours in order for the counting to be correct. For the #4 calculation part, it wanted to include the third time range that end with 8:59 AM as well as the next one that ends at 9:30 AM. I guess the comparison was just slightly off where column B was greater than column E. Weird. Adding the Time formulas cleared that up.

 

[Eric W]

Here's another take:

Book1
	A	B	C	D	E	F	G
1	Monday Time Ranges			Hours of Operation		Count of Monday Time Ranges
2	7:29	8:54		5:00	5:59	0	0
3	7:55	8:32		6:00	6:59	0	0
4	7:58	8:59		7:00	7:59	3	4
5	7:59	9:30		8:00	8:59	7	7
6	8:01	9:05		9:00	9:59	3	3
7	8:04	8:54		10:00	10:59	0	0
8	8:20	9:45		11:00	11:59	0	0
Sheet1

Cell Formulas
Range	Formula
F2:F8	F2	=SUMPRODUCT(--((E2*(E2<$B$2:$B$8)+$B$2:$B$8*(E2>=$B$2:$B$8))-(D2*(D2>$A$2:$A$8)+$A$2:$A$8*(D2<$A$2:$A$8))>0))
G2:G8	G2	=COUNTIFS($A$2:$A$8,">="&D2,$A$2:$A$8,"<="&E2+TIME(0,1,0),$B$2:$B$8,">="&D2,$B$2:$B$8,"<="&E2+TIME(0,1,0))+COUNTIFS($A$2:$A$8,"<="&D2,$A$2:$A$8,"<="&E2+TIME(0,1,0),$B$2:$B$8,">="&D2,$B$2:$B$8,"<="&E2+TIME(0,1,0))+COUNTIFS($A$2:$A$8,">="&D2,$A$2:$A$8,"<="&E2+TIME(0,1,0),$B$2:$B$8,">="&D2,$B$2:$B$8,">="&E2+TIME(0,1,0))+COUNTIFS($A$2:$A$8,"<="&D2,$A$2:$A$8,"<="&E2+TIME(0,1,0),$B$2:$B$8,">="&D2,$B$2:$B$8,">="&E2+TIME(0,1,0))

My formula in F2, shknbk2's is in G2. Mine's a bit shorter, but still pretty opaque. Mine shows 3 for the 7:00 hour due to the way it handles ties (7:59).

 

[AlphaFrog]

One more...

=SUMPRODUCT((HOUR($A$2:$A$8)<=HOUR(D2))*(HOUR($B$2:$B$8)>=HOUR(D2)))

 

[tbush33]

That worked! Thank you!