Formula for Date/Time Stamp Overlap

[bevo993071]

Can anyone help me with a formula to determine if two date/time stamps representing a range of time overlaps with another? I am been playing around with SUMPRODUCT however am unable to figure it out... I am dealing with multiple aircraft out of one base. See example below:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Departed[/TD]
[TD]Arrived[/TD]
[TD]Overlap
[/TD]
[/TR]
[TR]
[TD]5/1/15 04:30[/TD]
[TD]5/1/15 08:30[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]5/1/15 06:45[/TD]
[TD]5/1/15 06:45[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD]5/2/15 08:15[/TD]
[TD]5/2/15 09:15[/TD]
[TD]NO[/TD]
[/TR]
[TR]
[TD]5/2/15 13:15[/TD]
[TD]5/2/15 16:15[/TD]
[TD]NO[/TD]
[/TR]
</tbody>[/TABLE]


Thank you all

 

[DRSteele]

I fear I might not have done this right. Try it on your broader data and we'll see.

Excel 2012
	A	B	C
1	Departed	Arrived	Overlap
2	5/1/15 04:30	5/1/15 08:30	Yes
3	5/1/15 05:45	5/1/15 06:45	Yes
4	5/2/15 08:15	5/2/15 09:15	No
5	5/2/15 13:15	5/2/15 16:15	No
Sheet15

Cell Formulas
Range	Formula
C2	=IF(AND(SUMPRODUCT(--(A2<=$A$2:$A$5))>1,SUMPRODUCT(--(B2<=$B$2:$B$5))>1,SUMPRODUCT(--(A2<=$B$2:$B$5))>1,SUMPRODUCT(--(B2<=$A$2:$A$5))>1),"Yes","No")
C3	=IF(AND(SUMPRODUCT(--(A3<=$A$2:$A$5))>1,SUMPRODUCT(--(B3<=$B$2:$B$5))>1,SUMPRODUCT(--(A3<=$B$2:$B$5))>1,SUMPRODUCT(--(B3<=$A$2:$A$5))>1),"Yes","No")
C4	=IF(AND(SUMPRODUCT(--(A4<=$A$2:$A$5))>1,SUMPRODUCT(--(B4<=$B$2:$B$5))>1,SUMPRODUCT(--(A4<=$B$2:$B$5))>1,SUMPRODUCT(--(B4<=$A$2:$A$5))>1),"Yes","No")
C5	=IF(AND(SUMPRODUCT(--(A5<=$A$2:$A$5))>1,SUMPRODUCT(--(B5<=$B$2:$B$5))>1,SUMPRODUCT(--(A5<=$B$2:$B$5))>1,SUMPRODUCT(--(B5<=$A$2:$A$5))>1),"Yes","No")

 

[DRSteele]

Forget what I did there. I've been looking for a proper solution and I think this is it.

Source: Find overlapping date ranges in excel | Get Digital Help - Microsoft Excel resource

Excel 2012
	A	B	C
1	Departed	Arrived	Overlap
2	5/1/15 04:30	5/1/15 08:30	Yes
3	5/1/15 05:45	5/1/15 06:45	Yes
4	5/2/15 08:15	5/2/15 09:15	No
5	5/2/15 13:15	5/2/15 16:15	No
Sheet15

Cell Formulas
Range	Formula
C2	=IF(SUMPRODUCT((A2<$B$2:$B$5)*(B2>=$A$2:$A$5))>1,"Yes","No")
C3	=IF(SUMPRODUCT((A3<$B$2:$B$5)*(B3>=$A$2:$A$5))>1,"Yes","No")
C4	=IF(SUMPRODUCT((A4<$B$2:$B$5)*(B4>=$A$2:$A$5))>1,"Yes","No")
C5	=IF(SUMPRODUCT((A5<$B$2:$B$5)*(B5>=$A$2:$A$5))>1,"Yes","No")

 

[bevo993071]

The second worked perfectly, thank you for your help! Greatly appreciated

 

[DRSteele]

That's super! I'm glad it helped.