Overlapping Time Based on Conditions

[ericjspencley]

I have a report showing when an error light is above a set of conveyors. I want to figure out how much time a group of conveyors is down.

Lane 1 and 2 are run by Team 1
Lane 3 and 4 are run by Team 2
Lane 5 and 6 are run by Team 3
....
Lane 15 and 16 are run by Team 8
....
Lane 23 and 24 are run by Team 12

What I want to know, is for what length of time does each team have an error on both of their conveyors.

Capture.PNG

In the example I highlighted, Lane 5 and 6 both have error lights which overlap by 1.433 minutes.

[CAntosh]

Welcome to the forum! See if the attachment does the trick. I reformatted your sheet to display seconds in columns B and C, then I added a helper column labeled "Team" in Column F with a table off to the side to help match each entry to the associated team. The real work occurs in column G, which uses the following formula in G2, filled down through G357:

=IF(COUNTIFS($C$2:$C2,">"&$B2,$B$2:$B2,"<"&$C2,$F$2:$F2,$F2)>1,MIN(SUMPRODUCT(--($C$1:$C1>$B2),--($B$1:$B1<$C2),--($F$1:$F1=$F2),($C$1:$C1)),$C2)-$B2,"")

This should show the overlap time in the row of the second of the two overlapping entries. I didn't check every entry, but it seemed to work on the ones I looked at. Take a look, hopefully it helps...


EDIT: Better solutions below

[tim201110]

another way with min(ends)-max(starts)

[CAntosh]

Looking at Tim's solution, he made much better use of column D than I did. I overlooked the fact that my helper column already existed; there really wasn't any need for me to create the extra column and table. My cleaner version would look like this:

[ericjspencley]

thanks to both of you for the super fast solution!! Made my life a lot easier!