Find Time Overlaps ?

[theta]

Hi,

I have a data table as follows :

COLUMN A - Date
COLUMN B - Start
COLUMN C - Finish
COLUMN D - Total time

I need a formula to site in COLUMN L that will highlight (TRUE/FALSE) if there are overlaps in the times, specific to date.

THIS LIST IS SUBJECT TO SORTING AND SO THE FORMULA IN COLUMN F MUST USE OFFSET, or a full array to analyse the data and take into account the date.

e.g.

26/10/2009...09:00....13:00
26/10/2009...13:00....13:45
26/10/2009...13:40....16:00 overlap
26/10/2009...15:45....18:00 overlap
26/10/2009...18:15....19:00
27/10/2009...09:15....10:00
27/10/2009...10:00....13:00
27/10/2009...13:00....15:00
27/10/2009...15:00....16:00

Would be an amazing help!!!

Best regards

T

 

[dalavouras]

Welcome to the MrExcel board!

Does this do what you want? You may need to change the "200"s in the formula before copying down. That number needs to be big enough the reach at least the last row of your data.

Check overlap

	A	B	C	D	E
route
Route 3
Route 3
Route 3
Route 3
Route 3
Route 3
Route 3
Route 3
Route 3
Route 3
Route 3
Route 4
Route 4
Route 4
Route 4
Route 4
Route 4

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:103px;"><col style="width:85px;"><col style="width:74px;"><col style="width:68px;"><col style="width:56px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="align: right"]date[/TD]

[TD="align: right"]arrive[/TD]
[TD="align: right"]depart[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: right"]1/09/2010[/TD]

[TD="align: right"]8:41[/TD]
[TD="align: right"]8:59[/TD]
[TD="align: center"]check[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: right"]1/09/2010[/TD]

[TD="align: right"]8:55[/TD]
[TD="align: right"]10:00[/TD]
[TD="align: center"]check[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: right"]1/09/2010[/TD]

[TD="align: right"]9:35[/TD]
[TD="align: right"]10:00[/TD]
[TD="align: center"]check[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: right"]1/09/2010[/TD]

[TD="align: right"]11:02[/TD]
[TD="align: right"]11:25[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: right"]1/09/2010[/TD]

[TD="align: right"]9:15[/TD]
[TD="align: right"]9:28[/TD]
[TD="align: center"]check[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: right"]1/09/2010[/TD]

[TD="align: right"]9:45[/TD]
[TD="align: right"]10:00[/TD]
[TD="align: center"]check[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: right"]1/09/2010[/TD]

[TD="align: right"]11:55[/TD]
[TD="align: right"]12:12[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: right"]1/09/2010[/TD]

[TD="align: right"]12:33[/TD]
[TD="align: right"]12:39[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: right"]1/09/2010[/TD]

[TD="align: right"]13:22[/TD]
[TD="align: right"]13:37[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: right"]1/09/2010[/TD]

[TD="align: right"]14:07[/TD]
[TD="align: right"]14:16[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]
[TD="align: right"]1/09/2010[/TD]

[TD="align: right"]13:54[/TD]
[TD="align: right"]13:59[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]
[TD="align: right"]1/09/2010[/TD]

[TD="align: right"]9:55[/TD]
[TD="align: right"]10:25[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]
[TD="align: right"]1/09/2010[/TD]

[TD="align: right"]12:20[/TD]
[TD="align: right"]12:30[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]
[TD="align: right"]1/09/2010[/TD]

[TD="align: right"]8:45[/TD]
[TD="align: right"]9:30[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]
[TD="align: right"]1/09/2010[/TD]

[TD="align: right"]10:55[/TD]
[TD="align: right"]11:20[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]
[TD="align: right"]1/09/2010[/TD]

[TD="align: right"]14:10[/TD]
[TD="align: right"]14:40[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]18[/TD]
[TD="align: right"]1/09/2010[/TD]

[TD="align: right"]13:35[/TD]
[TD="align: right"]13:40[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]19[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula E2 =IF(COUNTA(A2:D2)=4,IF(SUMPRODUCT(--(A$2:A$200=A2),--(B$2:B$200=B2),(C$2:C$200<c2)< span="">*(D$2:D$200>C2)+(C$2:C$200<d2)< span="">*(D$2:D$200>D2))</d2)<>>0,"check","")</c2)<>,"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

That's great!!! Only problem is that the exact overlap does not alert us with check... Also for some reason in my worksheet only one of the two entries writes check and not both like i see in yours.

 

[dalavouras]

If we have a double entry, it doesn't alert us. For example


[TABLE="class: cms_table"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"]date[/TD]
[TD]route[/TD]
[TD="align: right"]arrive[/TD]
[TD="align: right"]depart[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]1/09/2010[/TD]
[TD]Route 3[/TD]
[TD="align: right"]8:41[/TD]
[TD="align: right"]8:59[/TD]
[TD="align: center"]check[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]1/09/2010[/TD]
[TD]Route 3[/TD]
[TD="align: right"]8:41[/TD]
[TD="align: right"]8:59[/TD]
[TD="align: center"]check[/TD]
[/TR]
</tbody>[/TABLE]
Is there a way to include double entry?

 

[pobysigo]

If we have a double entry, it doesn't alert us. For example


[TABLE="class: cms_table"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: right"]date[/TD]
[TD]route[/TD]
[TD="align: right"]arrive[/TD]
[TD="align: right"]depart[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]1/09/2010[/TD]
[TD]Route 3[/TD]
[TD="align: right"]8:41[/TD]
[TD="align: right"]8:59[/TD]
[TD="align: center"]check[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]1/09/2010[/TD]
[TD]Route 3[/TD]
[TD="align: right"]8:41[/TD]
[TD="align: right"]8:59[/TD]
[TD="align: center"]check[/TD]
[/TR]
</tbody>[/TABLE]
Is there a way to include double entry?

My problem excactly.
Does any of you smart people know how to solve that?
Or might be able to sugest another formula?
thanks a lot.

 

[ambush831]

Hi,

I also need help some help regarding venue reservation schedule. I want to know the availability of reservation attempts. I have date range and time range in a single row(reservation).
Here is my data:

[TABLE="width: 363"]
<colgroup><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Venue[/TD]
[TD]From Date[/TD]
[TD] End Date[/TD]
[TD]Start Time[/TD]
[TD] End Time[/TD]
[/TR]
[TR]
[TD]Hall[/TD]
[TD="align: right"]3/14/2019[/TD]
[TD="align: right"] 3/14/2019[/TD]
[TD="align: right"]9:00 AM[/TD]
[TD="align: right"]11:30 AM[/TD]
[/TR]
[TR]
[TD]Park[/TD]
[TD="align: right"]3/15/2019[/TD]
[TD="align: right"]3/16/2019[/TD]
[TD="align: right"]11:30 AM[/TD]
[TD="align: right"]1:30 PM[/TD]
[/TR]
[TR]
[TD]Hall[/TD]
[TD="align: right"]3/16/2019[/TD]
[TD="align: right"]3/17/2019[/TD]
[TD="align: right"]8:30 AM[/TD]
[TD="align: right"]2:30 PM[/TD]
[/TR]
</tbody>[/TABLE]

I need to figure out whether a venue is still available or not based on the existing data.
How can I identify if my reservation for a certain venue is possible (if no overlap of time between dates of a single row)? Else reservation will not be allowed Thanks for the help.