# Another Time comparison problem

#### SandsB

##### Well-known Member
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.

Last edited by a moderator:

#### Gerald Higgins

##### Well-known Member
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

##### Board Regular
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

##### Board Regular
...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

##### Board Regular
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";"")))

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!

Last edited:

1,082,139
Messages
5,363,363
Members
400,731
Latest member
Jackserver

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...