# Formula help for overlapping time periods

#### JJacobs

##### New Member
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

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### Domski

##### Well-known Member
Not sure if this does what you want...
Book1
ABCDEFG
1Staff IDDateStart TimeEnd TimeStartEndCheck
2105708/10/200610:30 AM11:00 AM08/10/2006 10:30 AM08/10/2006 11:00FALSE
3105708/10/200611:30 AM12:00 PM08/10/2006 11:30 AM08/10/2006 12:00FALSE
4105708/11/20069:00 AM10:15 AM08/11/2006 9:00 AM08/11/2006 10:15TRUE
5105708/10/200612:00 PM12:30 PM08/10/2006 12:00 PM08/10/2006 12:30TRUE
6105708/10/20061:30 PM3:00 PM08/10/2006 1:30 PM08/10/2006 15:00FALSE
7105708/10/20063:00 PM3:30 PM08/10/2006 3:00 PM08/10/2006 15:30FALSE
8105708/10/20063:30 PM4:00 PM08/10/2006 3:30 PM08/10/2006 16:00FALSE
9105708/10/20064:00 PM5:30 PM08/10/2006 4:00 PM08/10/2006 17:30TRUE
10105708/10/20069:45 AM11:00 AM08/10/2006 9:45 AM08/10/2006 11:00TRUE
11105708/11/200612:00 PM1:30 PM08/11/2006 12:00 PM08/11/2006 13:30FALSE
12105708/11/20061:45 PM2:15 PM08/11/2006 1:45 PM08/11/2006 14:15FALSE
13105708/11/20062:00 PM2:15 PM08/11/2006 2:00 PM08/11/2006 14:15FALSE
Sheet2

You never know...

#### barry houdini

##### MrExcel MVP
Try something like this...
Book2
ABCDEF
1StaffIDDateStart TimeEndTime
210578/10/200610:30 AM11:00 AMoverlap
310578/10/200611:30 AM12:00 PM
410578/11/20069:00 AM10:15 AM
510578/10/200612:00 PM12:30 PM
610578/10/20061:30 PM3:00 PM
710578/10/20063:00 PM3:30 PM
810578/10/20063:30 PM4:00 PM
910578/10/20064:00 PM5:30 PM
1010578/10/20069:45 AM11:00 AMoverlap
1110578/11/200612:00 PM1:30 PM
1210578/11/20061:45 PM2:15 PMoverlap
1310578/11/20062:00 PM2:15 PMoverlap
14
Sheet3

formula in E2 copied down is

=IF(SUMPRODUCT(--(A2=A\$2:A\$13),--(B2=B\$2:B\$13),1-((C2>=D\$2:D\$13)+(D2<=C\$2:C\$13)))>1,"overlap","")

Obviously you need to extend the ranges to take into account more than 13 rows...

#### JJacobs

##### New Member
Formular help for overlapping time periods

Thank you very much, Barry Houdini. That worked perfectly. I have noticed that it takes quite awhile for this formula to search all of my data. I can use this for over 60,000 rows of data. Is there a quicker way that this formula can be used?

Replies
2
Views
114
Replies
7
Views
250
Replies
3
Views
85
Replies
2
Views
243
Replies
1
Views
208

1,186,326
Messages
5,957,214
Members
438,293
Latest member
ginkycart

### 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.

### Which adblocker are you using?

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

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