Another Time comparison problem

[SandsB]

So I have thousands of rows of data every day. In column F I have an event code and in column G I have the duration of that event. I need to know if "Event B" occurs within 20 minutes of "Event A".

If
F2 is "Event A" and G2 is 0:09:23
and
F3 is "Event X" and G3 is 0:00:03
and
F4 is "Event Y" and G4 is 0:11:12
and
F5 is "Event B" and G5 is 0:05:00

Then Event B occurred (began) 0:11:15 after Event A so I'd like that cell colored red. The duration of Event B isn't an issue, just it's start time. The duration of Event A and it's start or end times themselves aren't an issue, I just need that duration of all the rows (could be 1 or up to 10) between the start of the first event AFTER Event A and the start of Event B.

And IF Event B is Red, Event A should be Yellow. Most of the time, these events will be farther apart so they wouldn't need to be colored.
And I'm not smart enough to do this.

 

[Gerald Higgins]

How do we know that Event B began 0:11:15 after Event A ?
It isn't clear that that's the case, in the data that you have provided.

Also, how do we know which events to compare against ?
Is it simply...
Check if Event B started within 20 minutes of Event A
Then Check if Event C started within 20 minutes of Event B
Then Check if Event D started within 20 minutes of Event C
and so on?

What if Event X started within 20 minutes of Event A ?
Is that important ?

 

[mayday1]

F2 is "Event A" and G2 is 0:09:23
F3 is "Event X" and G3 is 0:00:03
F4 is "Event Y" and G4 is 0:11:12
F5 is "Event B" and G5 is 0:05:00

The duration is in column G. So if Event A transitioned to Event X and X lasted 3 seconds and then Event Y happened and lasted 11:12, that adds up to 11:15 at which time Event B started.
We're only looking for a was to highlight the cells if B started within 20 minutes of the end of A.

 

[mayday1]

...and there could be many other events or no events between A and B. And this sequence could happen several times throughout the day - a typical day's file is about 1600 rows long and events A, B, C, D, etc happen several times a day. We just need to know when B happens after A within 20 minutes.

 

[petertenthije]

This might not be the most elegant solution, requiring two helper columns, but it works:

Column H
This column is used to make groupings of events. Every time Event A occurs a new group is created.
=COUNTIFS($F$1:F2;"Event A")

Column I
This column calculates the time between Event A and Event B, using the grouping created earlier.
The rounding has been added to avoid problems in case there are no events between Event A and Event B.
=ROUND(
IF(F3="Event B";SUMIFS($G:$G;$H:$H;COUNTIFS($F$1:F3;"Event B"))
-SUMIFS($G:$G;$F:$F;"Event A";$H:$H;$H3)
-SUMIFS($G:$G;$F:$F;"Event B";$H:$H;$H3)
);6)

Column J
This columns assigns a color coding to the event. This could be integrated in the conditional formatting.
=IF(SUMIFS(I:I;H:H;H2;F:F;"Event B")<=20/60/24;"";IF(F2="Event A";"Yellow";IF(F2="Event B";"red";"")))



edited to add
This does assume there is always an Event B before a new Event A is started!
Also, my version of excel uses semicolon instead of comma!