Results 1 to 10 of 10

Thread: Counting events

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Counting events

    I would like help with the following ...

    Let's say events occur regularly, say every 90 minutes, starting at midnight. Given two timestamps, I want to calculate how many times the event occurred from time 1 to time 2.

    Some examples:

    00:05 - 01:25 (1 hr 20 min): The event did not occur at all.
    00:05 - 01:45 (1 hr 40 min): The event occurred once at 01:30 am
    01:25 - 03:05 (also 1 hr 40 min): The event occurred twice at 01:30 and 03:00)

    Note that the same elapsed time (e..g. 1 hr 40 min) can encompass either 1 or two events.

    Any ideas?

  2. #2
    Board Regular
    Join Date
    Jul 2013
    Location
    Essex, UK
    Posts
    128
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting events

    If you can make a column of the times for your events, you could use the formula shown here:

    ABCDEFG
    1StartEndOcurrencesStart TimeInterval
    200:0501:25000:0001:30:00
    300:0501:45101:30
    401:2503:05203:00
    503:0004:40204:30
    606:0507:45106:00
    707:30
    809:00
    910:30
    1012:00
    1113:30
    1215:00
    1316:30
    1418:00
    1519:30
    1621:00
    1722:30

    Sheet1



    Worksheet Formulas
    CellFormula
    C2=COUNTIF($F$2:$F$17,">=" &A2)-COUNTIF($F$2:$F$17,">="&B2)
    F3=F2+$G$2



    I don't know how you would do it without the range of event times though - good luck!

    Helping you to Excel

  3. #3
    New Member
    Join Date
    Jun 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting events

    Here is my current attempt. It simply counts time spans ignoring the edge cases I gave examples of above.

    If the interval is 90 minutes that means there are 16 intervals in a day.

    Timestamps are recorded in this format: 6/5/2019 9:39:37 AM

    I subtract two timestamps (giving the number of days as a decimal number), multiply by 16 and keep only the integer value. Something like this: INT((T2-T1)*16)

    That sorta works, even for crossing a day boundary or spanning multiple days, but ignores those edge cases. I was hoping to get clever, maybe by adjusting T1 and T2 in some way or adjusting the final result based on where T1 and T2 are in relation to the 90 minute boundaries.

    Next I want to be able to set the start time so it isn't exactly midnight. That shouldn't be too hard once I have the above working, but one step at a time.

  4. #4
    New Member
    Join Date
    Jun 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting events

    P.S. I do have a table giving the time of each 90 minute period. COUNTIF might help but then I would need to work in the number of days (times 16) for long periods (multiple days).

  5. #5
    Board Regular
    Join Date
    Jul 2013
    Location
    Essex, UK
    Posts
    128
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting events

    I will give it some thought, but it'll have to wait till the morning now!

    Helping you to Excel

  6. #6
    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: Counting events

    Maybe something like this ?

    Code:
    =(A2-ROUNDDOWN(A1,0))/(90/1440)-MOD(A1,1)/(90/1440)
    where A1 contains start date and time, A2 contains finish date and time, and 90 is the minutes interval between events ?

    I haven't fully tested this, it seems to work for one or two simple cases.

    Edit to add - IF this approach works, then I think it might ONLY work for cases where the interval divides exactly into a day by a whole number, so that events always start at midnight.
    If you have intervals of odd numbers that don't divide neatly into 1440 - like 57 as a random example - so that each day the first interval is at a different time, then I think you will need a different approach.
    Last edited by Gerald Higgins; Jun 5th, 2019 at 06:32 PM.
    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

  7. #7
    Board Regular
    Join Date
    Jul 2013
    Location
    Essex, UK
    Posts
    128
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting events

    Wow Gerald, that maths is way out of my league! but sticking with my formula, counting events in a table, try this:
    Code:
    =COUNTIF($I$2:$I$17,">="&MOD(A3,1))-COUNTIF($I$2:$I$17,">="&VALUE("23:59:59"))+COUNT($I$2:$I$17)-COUNTIF($I$2:$I$17,">"&MOD(B3,1))+(INT(B3-A3)-1)*16
    This first checks to see if we span one or more dates. If not, it just looks up the number of events between start and end times, otherwise, it adds the number of events in the first day fragment, the number in the last day fragment, and 16 for any complete days in the middle.

    Helping you to Excel

  8. #8
    Board Regular
    Join Date
    Jul 2013
    Location
    Essex, UK
    Posts
    128
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting events

    Lovin' the signature, #Gerald , and totally agree! Pleased to say I knew what it would do before I ran it.

    Helping you to Excel

  9. #9
    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: Counting events

    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

  10. #10
    New Member
    Join Date
    Jun 2019
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Counting events

    Gerald and ClaireS. Thanks to the ideas you both posted I think I have a working solution that is quite straightforward.

    The general ideas I used were:
    1. Instead of 1440/90 I used the number of intervals per day. This is what is actually configured into the device I am monitoring.
    2. I used INT instead of ROUNDDOWN wherever an interval count is needed.
    3. Everything starts on an interval boundary, which helps simplify things considerably. This is OK until the occasional power failure which will restart things off-boundary in which case I need to do a reset on an interval boundary (not necessarily midnight).

    This results in the formula looking like this
    INT(A2*J2)-INT(A1*J2)
    Where the number of intervals for the day is in column J. Column A is the timestamp (day and hour).

    P.S. For some reason this board is NOT notifying me when there is a post so it took me some time to realize you both had made some useful posts.
    Last edited by bertilak; Jun 23rd, 2019 at 01:22 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
  •