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:

Function Peak(r As Range, dIntvl As Double) As Date
    ' Returns the date (& time) of the busiest interval of length dIntvl
    ' r is assumed as a single-column range containing the
    ' date and time of events in ascending order
    
    Dim iRow As Long, nRow As Long, mRow As Long
    Dim nEvnt As Long, nMax As Long
    Dim rFind As Range
    
    nRow = r.Rows.Count
    
    For iRow = 1 To r.Rows.Count
        Set rFind = r.Offset(iRow - 1).Resize(nRow - iRow + 1)
        nEvnt = WorksheetFunction.Match(r(iRow).Value + dIntvl, rFind, 1)
        If nEvnt > nMax Then
            Peak = r(iRow).Value
            nMax = nEvnt
        End If
        ' quit if no greater max is possible
        If nRow - iRow + 1 < nMax Then Exit For
    Next iRow

End Function

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

**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

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

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

With
B1:B50 containing Times

and
E1: 1st_Period_End_Time
E2: 12:01:00 AM

The below formulas check each 1-minute interval, 
beginning with the minute ending 12:01:00 AM, 
for a 24 hour period.

F1: Max_Interval_Count
F2: =MAX(FREQUENCY($B$3:$B$50,E2+TIME(0,ROW($A$1:$A$1440)-1,0)))

G1: Interval_Ending
(ARRAY FORMULA...Committed with Ctrl+Shift+Enter, instead of just Enter)
G2: =INDEX(E2+TIME(0,ROW($A$1:$A$1440)-1,0),MATCH(MAX(FREQUENCY($B$3:$B$50,E2+TIME(0,ROW($A$1:$A$1440)-1,0))),FREQUENCY($B$3:$B$50,E2+TIME(0,ROW($A$1:$A$1440)-1,0)),0))

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