Results 1 to 5 of 5

Thread: Another Time comparison problem
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Feb 2007
    Location
    Chicago
    Posts
    578
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Another Time comparison problem

    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 Fluff; Sep 10th, 2019 at 11:57 AM.
    Thanks for the help

  2. #2
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    9,084
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Another Time comparison problem

    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 ?
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  3. #3
    Board Regular mayday1's Avatar
    Join Date
    Oct 2007
    Location
    Dallas
    Posts
    238
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Another Time comparison problem

    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.
    Thanks for the tips

  4. #4
    Board Regular mayday1's Avatar
    Join Date
    Oct 2007
    Location
    Dallas
    Posts
    238
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Another Time comparison problem

    ...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.
    Thanks for the tips

  5. #5
    Board Regular
    Join Date
    Sep 2012
    Posts
    123
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Another Time comparison problem

    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 by petertenthije; Sep 10th, 2019 at 08:12 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •