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

MEUserII

Board Regular
Joined
Oct 27, 2017
Messages
81
Office Version
  1. 365
  2. 2021
  3. 2019
  4. 2016
  5. 2013
Platform
  1. Windows
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?
 
Last edited:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

James006

Well-known Member
Joined
Apr 4, 2009
Messages
3,680
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

Well-known Member
Joined
Dec 3, 2018
Messages
15,247
Office Version
  1. 2013
Platform
  1. Windows
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 />
 

Forum statistics

Threads
1,176,096
Messages
5,901,375
Members
434,887
Latest member
zoath

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