Counting Dynamic Time Intervals that Overlap with a Constant Time Interval?

[MEUserII]

Consider time interval: 08:00AM to 09:30AM which is to the constant time interval against which a dynamic list of other time intervals such as: 06:00AM to 07:30AM, 7:30AM to 09:00AM, 09:00AM to 10:30AM, 10:30AM to 12:00PM, and 07:30AM to 10:30AM; are to be checked against to see if any of the dynamic list of other time intervals overlaps with the constant time interval: 08:00AM to 09:30AM - how would you do this by formula, say by a COUNTIFS formula?

For reference, I attempted to do the following:
If for each of the dynamic list of time intervals, they are separated in to their beginning times and end times with: column A for beginning times and column B for end times; then you would have the following:
A2:A6 = 06:00AM, 07:30AM, 09:00AM, 10:30AM, 07:30AM
B2:B6 = 07:30AM, 09:00AM, 10:30AM, 12:00PM, 10:30AM

Using column C to convert A2:A6 to Excel timevalues and using column D to convert B2:B6 to Excel timevalues the following two formulas are entered in to C2/D2 and copied down to C6/D6.
C2 (copied down to C6) = TIMEVALUE((LEFT( ($A2), (5) ) )&(" ")&(RIGHT( ($A2), (2) ) ) )
D2 (copied down to D6) = TIMEVALUE((LEFT( ($B2), (5) ) )&(" ")&(RIGHT( ($B2), (2) ) ) )

So, my overall attempt with the two helper columns of C2:C6 and D2:D6 becomes this formula entered in E2:
=COUNTIFS( ($C$2:$C$6), ((">=")&(TIMEVALUE((LEFT( ("08:00AM"), (5) ) )&(" ")&(RIGHT( ("08:00AM"), (2) ) ) ) ) ), ($D$2:$D$6), (("<")&(TIMEVALUE((LEFT( ("09:30AM"), (5) ) )&(" ")&(RIGHT( ("09:30AM"), (2) ) ) ) ) ) )

So, that if the COUNTIFS formula above equals zero (=0); I would say "no overlap" and if the COUNTIFS formula above does not equal zero; I would say "yes overlaps".
However, this COUNTIFS formula does not seem to be right as the time interval: 07:30AM - 10:30AM is NOT counted, BUT still overlaps in the constant time interval: 08:30AM - 09:30AM.
So, how would I write a formula to check/count if the dynamic time intervals listed overlap with the constant time interval listed?

 

[James006]

Hello,

Could you clarify the structure of your sheet ... and your expected result ...

To calculate the amount of time overlapping, you will probably need some kind of Array formula ...

 

[DanteAmor]

I give you 2 possible solutions.
Whereas in columns A and B you have times.


The first solution:
In column C we can verify if the interval of A-B overlaps with the fixed interval E2-F2.
In cell D2, the count is performed to know the result.


The second solution, the formula is in cell G2, if there is an overlap interval, it gives you the result.

<b>Sheet</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:80px;" /><col style="width:80px;" /><col style="width:80px;" /><col style="width:86px;" /><col style="width:126px;" /><col style="width:116px;" /><col style="width:86px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Begin</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">End</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Overlaps</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Result</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Interval Time Begin</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Interval Time End</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">Result</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">06:00 a.m.</td><td style="text-align:right; ">07:30 a.m.</td><td style="text-align:right; ">0</td><td >Yes Overlaps</td><td style="text-align:right; ">08:00 a.m.</td><td style="text-align:right; ">09:30 a.m.</td><td >Yes Overlaps</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">07:30 a.m.</td><td style="text-align:right; ">09:00 a.m.</td><td style="text-align:right; ">1</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">09:00 a.m.</td><td style="text-align:right; ">10:30 a.m.</td><td style="text-align:right; ">1</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">10:30 a.m.</td><td style="text-align:right; ">12:00 p.m.</td><td style="text-align:right; ">0</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">07:30 a.m.</td><td style="text-align:right; ">10:30 a.m.</td><td style="text-align:right; ">1</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">08:30 a.m.</td><td style="text-align:right; ">09:00 a.m.</td><td style="text-align:right; ">1</td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formulas</td></tr><tr><td >C2</td><td >=IF(OR(AND(A2>=$E$2,A2<$F$2),AND(B2>$E$2,B2<=$F$2),AND($E$2>=A2,$E$2<B2),AND($F$2>A2,$F$2<=B2)),1,0)</td></tr><tr><td >D2</td><td >=IF(SUM(C2:C7)>0,"Yes Overlaps","No overlap")</td></tr><tr><td >G2</td><td >=IF(SUMPRODUCT(((A2:A7>=E2)*(A2:A7<F2))+((B2:B7>E2)*(B2:B7<=F2))+((A2:A7<=E2)*(B2:B7>E2))+((A2:A7<F2)*(B2:B7>=F2)))>0,"Yes Overlaps", "No Overlap")</td></tr></table></td></tr></table> <br /><br />