Find Time Overlaps ?

T

theta

Well-known Member
Joined
Jun 9, 2009
Messages
960
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Try this formula copied down.

Excel Workbook
ABCDE
126/10/20099:0013:00FALSE
226/10/200913:0013:45TRUE
326/10/200913:4016:00TRUE
426/10/200915:4518:00TRUE
526/10/200918:1519:00FALSE
627/10/20099:1510:00FALSE
727/10/200910:0013:00FALSE
827/10/200913:0015:00FALSE
927/10/200915:0016:00FALSE
Time Overlap
 
Upvote 0
That's great, thanks!

Was almost there but not been using SUMPRODUCT for so long lost my touch!

Regards

T
 
Upvote 0
Could you possibly explain the logic behind this please as hoping to develop it further

Regards

T
 
Upvote 0
Example data that breaks formula :

<TABLE style="WIDTH: 124pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=164 border=0><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #d8d8d8 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d8d8d8 0.5pt solid; WIDTH: 62pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: #f2f2f2" width=82 height=17>07:15</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d8d8d8; WIDTH: 62pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #f2f2f2" width=82>08:30</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #d8d8d8 0.5pt solid; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #fac090; BORDER-LEFT: #d8d8d8 0.5pt solid; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; HEIGHT: 12.75pt; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" height=17>08:45</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #fac090; BORDER-LEFT: #d8d8d8 0.5pt solid; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none">10:45</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl65 style="BORDER-RIGHT: #d8d8d8 0.5pt solid; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #f2f2f2; BORDER-LEFT: #d8d8d8 0.5pt solid; COLOR: black; BORDER-BOTTOM: #376091 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 12.75pt; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" height=17>08:45</TD><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #f2f2f2; BORDER-LEFT: #d8d8d8 0.5pt solid; COLOR: black; BORDER-BOTTOM: #376091 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none">18:00</TD></TR></TBODY></TABLE>

The bottom two should both be highlighted

<TABLE style="WIDTH: 124pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=164 border=0><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: #d8d8d8 0.5pt solid; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #fac090; BORDER-LEFT: #d8d8d8 0.5pt solid; WIDTH: 62pt; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; HEIGHT: 12.75pt; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" width=82 height=17>07:15</TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #fac090; BORDER-LEFT: #d8d8d8 0.5pt solid; WIDTH: 62pt; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" width=82>08:30</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: #d8d8d8 0.5pt solid; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #fac090; BORDER-LEFT: #d8d8d8 0.5pt solid; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; HEIGHT: 12.75pt; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" height=17>08:45</TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #fac090; BORDER-LEFT: #d8d8d8 0.5pt solid; COLOR: black; BORDER-BOTTOM: #d4d0c8; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none">10:45</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl67 style="BORDER-RIGHT: #d8d8d8 0.5pt solid; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #f2f2f2; BORDER-LEFT: #d8d8d8 0.5pt solid; COLOR: black; BORDER-BOTTOM: #376091 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 12.75pt; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none" height=17>07:15</TD><TD class=xl68 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #f2f2f2; BORDER-LEFT: #d8d8d8 0.5pt solid; COLOR: black; BORDER-BOTTOM: #376091 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; mso-pattern: black none; text-underline-style: none; text-line-through: none">18:00</TD></TR></TBODY></TABLE>

Or in the above, all 3 should be highlighted
 
Upvote 0
Hi

Try in D1:

=SUMPRODUCT(--((A1+C1<=$A$1:$A$15+$B$1:$B$15)+(A1+B1>=$A$1:$A$15+$C$1:$C$15)=0))<>1

Copy down

In this example I included the 6 cases from your last post at the end of your first table:


<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >C</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >D</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >E</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">2009-10-26</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">09:00</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">13:00</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">FALSE</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> <font color="#FF0000" > 2009-10-26</font></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> <font color="#FF0000" > 13:00</font></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> <font color="#FF0000" > 13:45</font></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">TRUE</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> <font color="#FF0000" >  </font></td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> <font color="#FF0000" > 2009-10-26</font></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> <font color="#FF0000" > 13:40</font></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> <font color="#FF0000" > 16:00</font></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">TRUE</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> <font color="#FF0000" >  </font></td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> <font color="#FF0000" > 2009-10-26</font></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> <font color="#FF0000" > 15:45</font></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> <font color="#FF0000" > 18:00</font></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">TRUE</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> <font color="#FF0000" >  </font></td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">2009-10-26</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">18:15</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">19:00</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">FALSE</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>6</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">2009-10-27</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">09:15</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">10:00</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">FALSE</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>7</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">2009-10-27</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">10:00</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">13:00</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">FALSE</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>8</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">2009-10-27</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">13:00</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">15:00</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">FALSE</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>9</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">2009-10-27</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">15:00</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">16:00</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">FALSE</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>10</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">2009-10-28</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">07:15</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; ">08:30</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">FALSE</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>11</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> <font color="#FF0000" > 2009-10-28</font></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> <font color="#FF0000" > 08:45</font></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> <font color="#FF0000" > 10:45</font></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">TRUE</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>12</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> <font color="#FF0000" > 2009-10-28</font></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> <font color="#FF0000" > 08:45</font></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> <font color="#FF0000" > 18:00</font></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">TRUE</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>13</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> <font color="#FF0000" > 2009-10-29</font></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> <font color="#FF0000" > 07:15</font></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> <font color="#FF0000" > 08:30</font></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">TRUE</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>14</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> <font color="#FF0000" > 2009-10-29</font></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> <font color="#FF0000" > 08:45</font></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> <font color="#FF0000" > 10:45</font></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">TRUE</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>15</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> <font color="#FF0000" > 2009-10-29</font></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> <font color="#FF0000" > 07:15</font></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> <font color="#FF0000" > 18:00</font></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:center;border-width: 1px;border-color:#888888; ">TRUE</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>16</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=6 style="background:#9CF; padding-left:1em" > [Book1]Sheet1</td></tr></table>
 
Upvote 0
This is an excellent solution, however I need to expand upon it's capabilities a bit to make it work in my instance. This is where I fall short.

I have the date, arrive, and depart times but I am recording them for multiple truck routes in a fleet. I am trying to develop a QA procedure and need to be able to see whether, within the same route and day, there are time overlaps - which would indicate an error in data input.

<table border="0" cellpadding="0" cellspacing="0" width="393"><col style="mso-width-source:userset;mso-width-alt:3949;width:81pt" width="108"> <col style="mso-width-source:userset;mso-width-alt:5741;width:118pt" width="157"> <col style="width:48pt" span="2" width="64"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:81pt" height="20" width="108">date</td> <td style="width:118pt" width="157">route</td> <td style="width:48pt" width="64">arrive</td> <td style="width:48pt" width="64">depart</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">01/09/2010</td> <td class="xl67">Route 3
</td> <td class="xl66" align="right">8:41</td> <td class="xl66" align="right">8:59</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">01/09/2010</td> <td class="xl67">Route 3
</td> <td class="xl66" align="right">8:55</td> <td class="xl66" align="right">10:00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">01/09/2010</td> <td class="xl67">Route 3
</td> <td class="xl66" align="right">9:35</td> <td class="xl66" align="right">10:00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">01/09/2010</td> <td class="xl67">Route 3
</td> <td class="xl66" align="right">11:02</td> <td class="xl66" align="right">11:25</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">01/09/2010</td> <td class="xl67">Route 3
</td> <td class="xl66" align="right">9:15</td> <td class="xl66" align="right">9:28</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">01/09/2010</td> <td class="xl67">Route 3
</td> <td class="xl66" align="right">9:45</td> <td class="xl66" align="right">10:00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">01/09/2010</td> <td class="xl67">Route 3
</td> <td class="xl66" align="right">11:55</td> <td class="xl66" align="right">12:12</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">01/09/2010</td> <td class="xl67">Route 3
</td> <td class="xl66" align="right">12:33</td> <td class="xl66" align="right">12:39</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">01/09/2010</td> <td class="xl67">Route 3
</td> <td class="xl66" align="right">13:22</td> <td class="xl66" align="right">13:37</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">01/09/2010</td> <td class="xl67">Route 3
</td> <td class="xl66" align="right">14:07</td> <td class="xl66" align="right">14:16</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">01/09/2010</td> <td class="xl67">Route 3
</td> <td class="xl66" align="right">13:54</td> <td class="xl66" align="right">13:59</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">01/09/2010</td> <td class="xl67">Route 4
</td> <td class="xl66" align="right">9:55</td> <td class="xl66" align="right">10:25</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">01/09/2010</td> <td class="xl67">Route 4
</td> <td class="xl66" align="right">12:20</td> <td class="xl66" align="right">12:30</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">01/09/2010</td> <td class="xl67">Route 4
</td> <td class="xl66" align="right">8:45</td> <td class="xl66" align="right">9:30</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">01/09/2010</td> <td class="xl67">Route 4
</td> <td class="xl66" align="right">10:55</td> <td class="xl66" align="right">11:20</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">01/09/2010</td> <td class="xl67">Route 4
</td> <td class="xl66" align="right">14:10</td> <td class="xl66" align="right">14:40</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" align="right" height="20">01/09/2010</td> <td class="xl67">Route 4
</td> <td class="xl66" align="right">13:35</td> <td class="xl66" align="right">13:40</td> </tr> </tbody></table>

In this case, the first two rows times overlap and would reveal the data input error. I need to be able to test for overlap within route 3 and then within route 4, and then on and on for hundreds more routes.
Can this be done within standard excel or do I need a VBA solution? Thanks!!
 
Upvote 0
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.

Excel Workbook
ABCDE
1dateroutearrivedepart
21/09/2010Route 38:418:59check
31/09/2010Route 38:5510:00check
41/09/2010Route 39:3510:00check
51/09/2010Route 311:0211:25
61/09/2010Route 39:159:28check
71/09/2010Route 39:4510:00check
81/09/2010Route 311:5512:12
91/09/2010Route 312:3312:39
101/09/2010Route 313:2213:37
111/09/2010Route 314:0714:16
121/09/2010Route 313:5413:59
131/09/2010Route 49:5510:25
141/09/2010Route 412:2012:30
151/09/2010Route 48:459:30
161/09/2010Route 410:5511:20
171/09/2010Route 414:1014:40
181/09/2010Route 413:3513:40
19
Check overlap
 
Last edited:
Upvote 0
Not sure if I can ask in this thread as I am a new to here. I have this problem similar with the above discussions but the only different is that the overlapping time for particular product. How can I do this? Thanks for your help
ABC
Product IDStart Time 1End Time 2
1F2383812:38 pm01:59 pm
2F238381:50 PM04:04 pm
3F2383803:16 pm04:06 pm
4F2383804:06 pm04:08 pm
5F2383808:00 am09:30 am
6F2396102:59 pm04:04 pm
7F2396110:19 am12:00 pm
8F2396112:30 pm04:06 pm
9F2396104:06 pm04:48 pm
10F2396108:24 am09:55 am
11F2427008:05 am10:00 am
12F2427010:15 am10:35 am
13F2427010:18 AM12:00 pm
14F2427012:30 pm12:34 pm

<COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><TBODY>
</TBODY>
 
Upvote 0
You must log in or register to reply here.

Similar threads

AOB
  • Question
Calculate the "true" duration of up/down times accounting for gaps and overlaps
Replies
1
Views
279
AOB
AOB
K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
322
awoohaw
awoohaw
A
  • Question
Need to convert days, hours, minutes and second text into hh:mm:ss
Replies
7
Views
120
Aadya
A
D
  • Solved
Help with rank and difference.
Replies
3
Views
70
Fluff
Fluff
J
  • Question
How to Find Time Overlaps
2
Replies
10
Views
467
JK_NMS
J

Forum statistics

Threads
1,215,200
Messages
6,123,611
Members
449,109
Latest member
Sebas8956

Share this page

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
Back
Top