Overlap between two time ranges

[sae1984]

Hi I have three columns A, B , C, where A has number of days, B has a starting time and C has and end time

Day3 07:23:43​ 16:27:01​ Day2 19:28:33​ 20:09:55​ Day2 09:00:33​ 19:13:55​ Day5 08:00:03​ 16:03:23​

I want to check if one time range (from starting time to end time, B-C) overlaps nay of the other time ranges in the specific day, and how many times. Because this is a sample of data and possibly columns b and c will be larger.

Thanks a lot

 

[Eric W]

Welcome to MrExcel!

Try:

Book1
	A	B	C	D
1	Day	Start	End	Overlaps
2	Day3	7:23:43	16:27:01	0
3	Day2	19:28:33	20:09:55	1
4	Day2	9:00:33	19:13:55	0
5	Day5	8:00:03	16:03:23	0
6	Day2	19:30:01	19:31:02	1
7	Day7	13:00:00	13:01:00	0
8	Day7	14:00:00	14:01:00	1
9	Day7	13:59:00	14:00:30	1
10
Sheet2

Cell Formulas
Range	Formula
D2:D9	D2	=SUM(IF(A$2:A$20=A2,IF(IF(C2<C$2:C$20,C2,C$2:C$20)-IF(B2>B$2:B$20,B2,B$2:B$20)>0,1)))-1
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[sae1984]

Thanks a lot. Its working. Thanks again

 

[Eric W]

Glad it works for you. Thanks for the feedback. ?

 

[Pulsar3000]

Hi Everyone and @Eric W

I have a similar question.

I have to identify overlapping times. Tried an INDEX MATCH and my formulation didn't work. Also, an IF function but get SPILL Error in columns L & O.

What I need:
If both columns E & I match the respective row, I need to check for overlapping times in columns J & K for the time range in the respective row and put a 1 or "Yes" as an identifier. I want all rows with overlapping times identified.

The original raw data is in Columns A - C in case you'd like to rework the Start and End Dates. Note that if a shift starts in PM and Ends in AM in Column C, it's the next day as the date in Column B is the "Start Date" essentially.

Thank you in advance!

Aya Listing.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	Date	Shift	Person + Date + Shift	Person + Start Date	OType	Type	End Date	Person + End Date	Start Time	End Time	Start Time Overlap Index?	Start Time Overlap IF	End Time Overlap Index?	Start Time Overlap IF
2	Person One	1/28/2021	07:00 PM-07:30 PM	Person One 4422407:00 PM-07:30 PM	Person One 44224	1	Something	1/28/2021	Person One 44224	19:00	19:30	4.00	0	5.00	0
3	Person One	1/28/2021	12:00 PM-07:00 PM	Person One 4422412:00 PM-07:00 PM	Person One 44224	1	Something	1/28/2021	Person One 44224	12:00	19:00	4.00	0	4.00	0
4	Person One	1/28/2021	07:00 AM-07:30 PM	Person One 4422407:00 AM-07:30 PM	Person One 44224	1	Something	1/28/2021	Person One 44224	7:00	19:30	5.00	1	5.00	0
5	Person One	1/28/2021	07:00 AM-07:30 PM	Person One 4422407:00 AM-07:30 PM	Person One 44224	1	Something	1/28/2021	Person One 44224	7:00	19:30	5.00	1	5.00	0
6	Person One	1/28/2021	07:00 AM-12:00 PM	Person One 4422407:00 AM-12:00 PM	Person One 44224	1	Something	1/28/2021	Person One 44224	7:00	12:00	5.00	0	4.00	0
7	Person Two	1/28/2021	11:30 AM-07:00 PM	Person Two 4422411:30 AM-07:00 PM	Person Two 44224	1	Something	1/28/2021	Person Two 44224	11:30	19:00	5.00	#SPILL!	5.00	#SPILL!
8	Person Two	1/28/2021	07:00 AM-07:00 PM	Person Two 4422407:00 AM-07:00 PM	Person Two 44224	1	Something	1/28/2021	Person Two 44224	7:00	19:00	5.00		5.00
9	Person Two	1/28/2021	07:00 AM-07:00 PM	Person Two 4422407:00 AM-07:00 PM	Person Two 44224	1	Something	1/28/2021	Person Two 44224	7:00	19:00	5.00		5.00
10	Person Two	1/28/2021	07:00 AM-11:30 AM	Person Two 4422407:00 AM-11:30 AM	Person Two 44224	1	Something	1/28/2021	Person Two 44224	7:00	11:30	5.00		5.00
11	Person Three	1/21/2021	06:45 PM-12:00 AM	Person Three 4421706:45 PM-12:00 AM	Person Three 44217	1	Something	1/21/2021	Person Three 44217	18:45	0:00	5.00	FALSE	5.00	FALSE
12	Person Three	1/21/2021	07:15 AM-07:30 AM	Person Three 4421707:15 AM-07:30 AM	Person Three 44217	1	Something	1/21/2021	Person Three 44217	7:15	7:30	5.00	FALSE	5.00	FALSE
13	Person Three	1/21/2021	12:00 AM-07:15 AM	Person Three 4421712:00 AM-07:15 AM	Person Three 44217	1	Something	1/21/2021	Person Three 44217	0:00	7:15	5.00	FALSE	5.00	FALSE
Sheet7

Cell Formulas
Range	Formula
D2:D13	D2	=A2&B2&C2
E2:E13	E2	=A2&B2
H2:H13	H2	=IF(AND(MID(C2,7,2)="PM",RIGHT(C2,2)="AM",(MID(C2,10,2)*1)<12)=TRUE,B2+1,B2)
I2:I13	I2	=A2&H2
J2:J13	J2	=LEFT(C2,8)+12
K2:K13	K2	=RIGHT(C2,8)+12
L2:L13	L2	=MATCH(1,($E$2:$E$6=E2)*($I$2:$I$6=I2)*($J$2:$J$6<J2)*($K$2:$K$6>J2))
M11:M15,M2:M7	M2	=IF($E$2:$E$6=E2,IF($I$2:$I$6=I2,IF($J$2:$J$6<J2,IF($K$2:$K$6>J2,1,0),0)))
O11:O15,O2:O7	O2	=IF($E$2:$E$6=E2,IF($I$2:$I$6=I2,IF($J$2:$J$6<K2,IF($K$2:$K$6>K2,1,0),0)))
N2:N13	N2	=MATCH(1,($E$2:$E$6=E2)*($I$2:$I$6=I2)*($J$2:$J$6<K2)*($K$2:$K$6>K2))
Dynamic array formulas.

 

[Eric W]

Your formulas are kind of all over the place. I'd definitely rework your layout. But on the shift range column (C), is a shift of 07:00 PM-07:30 PM a .5 hour shift, or a 24.5 hour shift? Same question, line 12. Is there a minimum number of hours for a shift?

 

[Eric W]

Assuming each shift must be at least 3 hours:

Book1
	A	B	C	D	E	F	G	H
1	Name	Date	Shift	OType	Type	Start	End	Overlap?
2	Person One	1/28/2021	07:00 PM-07:30 PM	1	Something	1/28/21 7:00 PM	1/29/21 7:30 PM	TRUE
3	Person One	1/28/2021	12:00 PM-07:00 PM	1	Something	1/28/21 12:00 PM	1/28/21 7:00 PM	TRUE
4	Person One	1/28/2021	07:00 AM-07:30 PM	1	Something	1/28/21 7:00 AM	1/28/21 7:30 PM	TRUE
5	Person One	1/28/2021	07:00 AM-07:30 PM	1	Something	1/28/21 7:00 AM	1/28/21 7:30 PM	TRUE
6	Person One	1/28/2021	07:00 AM-12:00 PM	1	Something	1/28/21 7:00 AM	1/28/21 12:00 PM	TRUE
7	Person Two	1/28/2021	11:30 AM-07:00 PM	1	Something	1/28/21 11:30 AM	1/28/21 7:00 PM	TRUE
8	Person Two	1/28/2021	07:00 AM-07:00 PM	1	Something	1/28/21 7:00 AM	1/28/21 7:00 PM	TRUE
9	Person Two	1/28/2021	07:00 AM-07:00 PM	1	Something	1/28/21 7:00 AM	1/28/21 7:00 PM	TRUE
10	Person Two	1/28/2021	07:00 AM-11:30 AM	1	Something	1/28/21 7:00 AM	1/28/21 11:30 AM	TRUE
11	Person Three	1/21/2021	06:45 PM-12:00 AM	1	Something	1/21/21 6:45 PM	1/22/21 12:00 AM	TRUE
12	Person Three	1/21/2021	07:15 AM-07:30 AM	1	Something	1/21/21 7:15 AM	1/22/21 7:30 AM	TRUE
13	Person Three	1/21/2021	12:00 AM-07:15 AM	1	Something	1/21/21 12:00 AM	1/21/21 7:15 AM	FALSE
Sheet8

Cell Formulas
Range	Formula
F2:F13	F2	=B2+LEFT(C2,8)
G2:G13	G2	=B2+RIGHT(C2,8)+(RIGHT(C2,8)+0<LEFT(C2,8)+0.125)
H2:H13	H2	=SUM(--(IF((A2=$A$2:$A$13)*(ROW(A2)<>ROW($A$2:$A$13)),IF(G2<$G$2:$G$13,G2,$G$2:$G$13)-IF(F2>$F$2:$F$13,F2,$F$2:$F$13),0)>0))>0

 

[Pulsar3000]

Your formulas are kind of all over the place. I'd definitely rework your layout. But on the shift range column (C), is a shift of 07:00 PM-07:30 PM a .5 hour shift, or a 24.5 hour shift? Same question, line 12. Is there a minimum number of hours for a shift?

Hi @Eric W

There is no minimum number of hours per shift and there are no shifts for 24 hours or more.

 

[Eric W]

In that case, change the G2 formula to:

=B2+RIGHT(C2,8)+(RIGHT(C2,8)+0<LEFT(C2,8)+0)

 

[Pulsar3000]

In that case, change the G2 formula to:

=B2+RIGHT(C2,8)+(RIGHT(C2,8)+0<LEFT(C2,8)+0)

@Eric W

The fornula seems to work so thank you for your help!

The issue I have now is that it seems to crash my Excel using it with a range with 90,000+ rows even with "calculate threads" enabled.

By any chance, is there an alternative that can handle such large range? I need to use this with other data sets with over 100,000 rows.

Sorry for not mentioning the large data sets I'm working with before.

I'll check Power Query to see if I can find something on that end but prefer an Excel function, if possible.