Formula help for overlapping time periods

[JJacobs]

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

 

[Domski]

Not sure if this does what you want...

Book1
	A	B	C	D	E	F	G
1	Staff ID	Date	Start Time	End Time	Start	End	Check
2	1057	08/10/2006	10:30 AM	11:00 AM	08/10/2006 10:30 AM	08/10/2006 11:00	FALSE
3	1057	08/10/2006	11:30 AM	12:00 PM	08/10/2006 11:30 AM	08/10/2006 12:00	FALSE
4	1057	08/11/2006	9:00 AM	10:15 AM	08/11/2006 9:00 AM	08/11/2006 10:15	TRUE
5	1057	08/10/2006	12:00 PM	12:30 PM	08/10/2006 12:00 PM	08/10/2006 12:30	TRUE
6	1057	08/10/2006	1:30 PM	3:00 PM	08/10/2006 1:30 PM	08/10/2006 15:00	FALSE
7	1057	08/10/2006	3:00 PM	3:30 PM	08/10/2006 3:00 PM	08/10/2006 15:30	FALSE
8	1057	08/10/2006	3:30 PM	4:00 PM	08/10/2006 3:30 PM	08/10/2006 16:00	FALSE
9	1057	08/10/2006	4:00 PM	5:30 PM	08/10/2006 4:00 PM	08/10/2006 17:30	TRUE
10	1057	08/10/2006	9:45 AM	11:00 AM	08/10/2006 9:45 AM	08/10/2006 11:00	TRUE
11	1057	08/11/2006	12:00 PM	1:30 PM	08/11/2006 12:00 PM	08/11/2006 13:30	FALSE
12	1057	08/11/2006	1:45 PM	2:15 PM	08/11/2006 1:45 PM	08/11/2006 14:15	FALSE
13	1057	08/11/2006	2:00 PM	2:15 PM	08/11/2006 2:00 PM	08/11/2006 14:15	FALSE
Sheet2

You never know...

 

[barry houdini]

Try something like this...

Book2
	A	B	C	D	E	F
1	StaffID	Date	Start Time	EndTime
2	1057	8/10/2006	10:30 AM	11:00 AM	overlap
3	1057	8/10/2006	11:30 AM	12:00 PM
4	1057	8/11/2006	9:00 AM	10:15 AM
5	1057	8/10/2006	12:00 PM	12:30 PM
6	1057	8/10/2006	1:30 PM	3:00 PM
7	1057	8/10/2006	3:00 PM	3:30 PM
8	1057	8/10/2006	3:30 PM	4:00 PM
9	1057	8/10/2006	4:00 PM	5:30 PM
10	1057	8/10/2006	9:45 AM	11:00 AM	overlap
11	1057	8/11/2006	12:00 PM	1:30 PM
12	1057	8/11/2006	1:45 PM	2:15 PM	overlap
13	1057	8/11/2006	2:00 PM	2:15 PM	overlap
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]

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?