Another Time comparison problem

SandsB

Well-known Member
Joined
Feb 13, 2007
Messages
579
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
Joined
Mar 26, 2007
Messages
9,115
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
Joined
Oct 5, 2007
Messages
241
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
Joined
Oct 5, 2007
Messages
241
...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
Joined
Sep 25, 2012
Messages
148
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!
 
Last edited:

Forum statistics

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

Some videos you may like

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...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top