Formula Finding Peak Period in Time Log

**wrongrobot** · 10-29-2007, 08:39 PM

Hello!
FAR from an experienced Excel user here.
We have a specific formula objective, which i haven't been able to solve on my own. Perhaps someone here can assist...

We have time logs of activity, such as you would expect in a security station. Individuals arrive and are timestamped. We are seeking to find the number of users passing during a peak 5-minute period. I have looked at charting the data to look visually for these peak points in order to narrow our focus, but want to be able to calculate same.
We have columns in our spreadsheet for entries and their timestamps.
Any thoughts, or directions you might be able to point me in?
thanks!
thom

**starguy** · 10-30-2007, 01:36 AM

It would be better if you post a sample file with some data here in zip format.

**wrongrobot** · 10-30-2007, 01:34 PM

Here's a small 10 entry sample. Thanks for the assist!

Column A= Date
Column B = Time
Column C= ID

All we really want to do is be able to identify a peak 5 minute period in each day...
t

**MickeyGreen** · 10-30-2007, 02:31 PM

This is an interesting challenge. I'd like to hear what others think but I don't see how you do it without defining discrete time intervals and adding up the number of time stamps in that interval. If you settle on 5 minute intervals starting 1 minute apart, you end up evaluating 1,440 intervals per day (60*24).

Ex. 8:00:00 to 8:04:59
8:01:00 to 8:05:59
8:02:00 to 8:06:59
etc.

You calculate the number of stamps between intervals and take the max.

If you start the intervals 15 seconds apart you'd have 4 times as many to evaluate. And so on.

**shg** · 10-30-2007, 02:49 PM

I think we'd need daddylonglegs for a worksheet function-based solution.

In the meanwhile, here's a UDF:

Please Login or Register  to view this content.

Example worksheet usage is =Peak(rangeContainingTimes, "0:05")

**wrongrobot** · 10-31-2007, 07:15 PM

Thanks for your help!
As an administrator of a forum, I know it's irritating when folks pop in and out, but more so when they don't bother to acknowledge your assistance on the way out... so thanks for giving this a go for me! Happy halloween...

**Ron Coderre** · 10-31-2007, 08:06 PM

Sorry for being late to this post.....but here's a possible formula solution:

Please Login or Register  to view this content.

Does that help?
Post back if you have more questions.

**shg** · 10-31-2007, 08:08 PM

I do indeed appreciate it, wrongrobot, thanks.

**daddylonglegs** · 10-31-2007, 08:45 PM

Just saw Ron's post, this works along similar lines.....

Assuming you look at fixed 5 minute intervals throughout the day,

00:00:00 to 00:04:59
00:05:00 to 00:09:59

and so on, i.e. 288 periods per day

You could do the following:

Assuming a sightly larger dataset with dates in A3:A1200 and times in B3:B1200 then for a specific date in E3 this formula will give you the maximum number of occurrences within any of those 5 minute periods:

=MAX(FREQUENCY(IF(A$3:A$1200=E3,INT(B$3:B$1200*288)+1),ROW(INDIRECT("1:287"))))

confirmed with CTRL+SHIFT+ENTER

and, assuming the above formula in G3 this formula will give the start of the time period

=(MATCH(G3,FREQUENCY(IF(A$3:A$1200=E3,INT(B$3:B$1200*288)+1),ROW(INDIRECT("1:287"))),0)-1)/288

also confirmed with CTRL+SHIFT+ENTER

see attached