I am working with thousands of rows of data on a single worksheet and am searching for a formula to identify instances when start and stop times overlap with other rows. Each row contains a staff id#, event date, event start time and event end time. As an example, if row four shows staff #1507 attended an event on 8/10 from 9:00 AM to 10:15 AM and row ten shows the same staff also attended another event on 8/10 from 9:45 AM to 11:00 AM, I need a formula that will identify both rows as overlapping. I have tried multiple formulas but have not been able to get anything to really work. I’m attaching a excerpt from my worksheet and hoping that someone can help me. Thanks.
StaffID Date Start Time EndTime
1057 8/10/2006 10:30 AM 11:00 AM
1057 8/10/2006 11:30 AM 12:00 PM
1057 8/11/2006 9:00 AM 10:15 AM
1057 8/10/2006 12:00 PM 12:30 PM
1057 8/10/2006 1:30 PM 3:00 PM
1057 8/10/2006 3:00 PM 3:30 PM
1057 8/10/2006 3:30 PM 4:00 PM
1057 8/10/2006 4:00 PM 5:30 PM
1057 8/10/2006 9:45 AM 11:00 AM
1057 8/11/2006 12:00 PM 1:30 PM
1057 8/11/2006 1:45 PM 2:15 PM
1057 8/11/2006 2:00 PM 2:15 PM
StaffID Date Start Time EndTime
1057 8/10/2006 10:30 AM 11:00 AM
1057 8/10/2006 11:30 AM 12:00 PM
1057 8/11/2006 9:00 AM 10:15 AM
1057 8/10/2006 12:00 PM 12:30 PM
1057 8/10/2006 1:30 PM 3:00 PM
1057 8/10/2006 3:00 PM 3:30 PM
1057 8/10/2006 3:30 PM 4:00 PM
1057 8/10/2006 4:00 PM 5:30 PM
1057 8/10/2006 9:45 AM 11:00 AM
1057 8/11/2006 12:00 PM 1:30 PM
1057 8/11/2006 1:45 PM 2:15 PM
1057 8/11/2006 2:00 PM 2:15 PM