Count Overlapping Time Ranges per Hour

tbush33

New Member
Joined
Oct 22, 2017
Messages
6
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 RangesHours of OperationCount of Monday Time Ranges
7:298:545:005:590
7:558:326:006:590
7:588:597:007:594
7:599:308:008:597
8:019:059:009:594
8:048:5410:0010:590
8:209:4511:0011:590


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

Any idea how I would do this?
 

Some videos you may like

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
Joined
Mar 5, 2016
Messages
248
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.
 
Solution

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,746
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
Joined
Sep 2, 2009
Messages
16,400
One more...

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

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top