# Count Overlapping Time Ranges per Hour

#### tbush33

##### New Member
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?

### Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

#### shknbk2

##### Board Regular
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.

Excel Formula:
``=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

##### MrExcel MVP
Here's another take:

Book1
ABCDEFG
1Monday Time RangesHours of OperationCount of Monday Time Ranges
27:298:545:005:5900
37:558:326:006:5900
47:588:597:007:5934
57:599:308:008:5977
68:019:059:009:5933
78:048:5410:0010:5900
88:209:4511:0011:5900
Sheet1
Cell Formulas
RangeFormula
F2:F8F2=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:G8G2=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).

Last edited:

#### AlphaFrog

##### MrExcel MVP
One more...

Excel Formula:
``=SUMPRODUCT((HOUR(\$A\$2:\$A\$8)<=HOUR(D2))*(HOUR(\$B\$2:\$B\$8)>=HOUR(D2)))``

Last edited:

#### tbush33

##### New Member
That worked! Thank you!

Replies
5
Views
65
Replies
4
Views
207
Replies
1
Views
58
Replies
13
Views
229
Replies
1
Views
51

1,127,172
Messages
5,623,178
Members
415,955
Latest member
Footballtend

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back