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!!