Formula help for overlapping time periods

JJacobs

New Member
Joined
Oct 9, 2006
Messages
2
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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Oct 9, 2006
Messages
2
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?
 

Forum statistics

Threads
1,136,649
Messages
5,676,990
Members
419,667
Latest member
MegEri

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
Top