Counting events

[bertilak]

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?

 

[ClaireS]

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

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]

Book1
	A	B	C	D	E	F	G
1	Start	End	Ocurrences			Start Time	Interval
2	00:05	01:25	0			00:00	01:30:00
3	00:05	01:45	1			01:30
4	01:25	03:05	2			03:00
5	03:00	04:40	2			04:30
6	06:05	07:45	1			06:00
7						07:30
8						09:00
9						10:30
10						12:00
11						13:30
12						15:00
13						16:30
14						18:00
15						19:30
16						21:00
17						22:30
Sheet1

Cell Formulas
Range	Formula
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!
[/FONT]

 

[bertilak]

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.

 

[bertilak]

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).

 

[ClaireS]

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

 

[Gerald Higgins]

Maybe something like this ?

=(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.

 

[ClaireS]

Wow Gerald, that maths is way out of my league! but sticking with my formula, counting events in a table, try this:

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=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[/FONT]

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.

 

[ClaireS]

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

 

[Gerald Higgins]

 

[bertilak]

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.