You should re-copy the code.

You have missed a line.

Summary = Summary & "There were " & Frequency(X) & " times " & X & _
" lines were in use at the same time." & vbNewLine
Next


Gord Dibben MS Excel MVP

On Sat, 20 Feb 2010 13:56:40 -0800, Murali Thoota wrote:

Hi, I have the same requirement and I was running your macro. It is giving me a syntax error. I am using excel 2007.

The error is at
Summary = Summary & "There were " & Frequency(X) & " times " & X & _
Next

What should I do ?



Rick Rothstein \(MVP - VB\) wrote:

Okay, here is my take on it.
29-Mar-08

Okay, here is my take on it. Assuming your start date/time is in Column A,
your duration is in Column B, and your ending date/time is in Column C, run
the following macro from the code page that your data is located on. When
the code is finished running (I have no idea how long it will take to
process 250,000 rows of data), a message box will appear showing the usage
frequency on a per second basis.

Rick

Sub GetMaxUsageBySecond()
Dim X As Long
Dim Z As Long
Dim Offset As Long
Dim LastRow As Long
Dim TotalSeconds As Long
Dim MaxStartTimeSeconds As Long
Dim Duration As Long
Dim MaxSeconds As Long
Dim Seconds() As Long
Dim Frequency() As Long
Dim FirstDateTime As Double
Dim LastDateTime As Double
Dim LastStartTime As Double
Dim MaxTime As Date
Dim MaximumDate As Date
Dim Summary As String
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
FirstDateTime = Range("A1").Value
LastDateTime = Cells(LastRow, "C").Value
LastStartTime = Cells(LastRow, "A").Value
MaxStartTimeSeconds = DateDiff("s", FirstDateTime, LastStartTime)
MaximumDate = Application.Evaluate("=MAX(C1:C" & _
Cells(Rows.Count, "C").End(xlUp).Row & ")")
TotalSeconds = DateDiff("s", FirstDateTime, MaximumDate)
ReDim Seconds(0 To TotalSeconds)
For X = 1 To LastRow
Duration = Cells(X, "B").Value
Offset = DateDiff("s", FirstDateTime, Cells(X, "A").Value)
For Z = Offset To Offset + Duration
Seconds(Z) = Seconds(Z) + 1
Next
Next
For X = 0 To TotalSeconds
If Seconds(X) MaxSeconds Then
MaxSeconds = Seconds(X)
MaxTime = FirstDateTime + TimeSerial(0, 0, X)
End If
Next
ReDim Frequency(0 To MaxSeconds)
For X = 0 To TotalSeconds
Frequency(Seconds(X)) = Frequency(Seconds(X)) + 1
Next
For X = MaxSeconds To 0 Step -1
Summary = Summary & "There were " & Frequency(X) & " times " & X & _
" lines were in use at the same time." & vbNewLine
Next
MsgBox Summary
End Sub






wrote in message
...

Previous Posts In This Thread:

On Saturday, March 29, 2008 7:29 AM
nathaniel.watkin wrote:

Calculate actual trunk utilization based on call start/duration
I am 'attempting' to calculate our phone line utilization at work. The
end goal is to determine how many lines we actually use at any given
point. I thought this would be an easy problem, however the more I
work on solving it, the more difficult it becomes.



I have roughly 250,000 calls (I'm obviously using excel 2007) that I
am attempting to run calculations against. Here is a sampling of the
data.



Start Duration End

3/19/07 6:39:59 AM 143 3/19/07 6:42:22 AM
3/19/07 6:45:7 AM 9 3/19/07 6:45:16 AM
3/19/07 6:54:25 AM 14 3/19/07 6:54:39 AM
3/19/07 7:03:25 AM 50 3/19/07 7:04:15 AM
3/19/07 7:04:4 AM 18 3/19/07 7:04:22 AM
3/19/07 7:07:6 AM 25 3/19/07 7:07:31 AM
3/19/07 7:10:41 AM 267 3/19/07 7:15:8 AM
3/19/07 7:12:30 AM 29 3/19/07 7:12:59 AM
3/19/07 7:12:41 AM 18 3/19/07 7:12:59 AM
3/19/07 7:37:45 AM 37 3/19/07 7:38:22 AM
3/19/07 7:42:50 AM 9 3/19/07 7:42:59 AM
3/19/07 7:43:37 AM 7 3/19/07 7:43:44 AM
3/19/07 7:44:58 AM 43 3/19/07 7:45:41 AM
3/19/07 7:53:46 AM 75 3/19/07 7:55:1 AM
3/19/07 7:56:30 AM 34 3/19/07 7:57:4 AM
3/19/07 7:59:38 AM 312 3/19/07 8:04:50 AM
3/19/07 8:00:2 AM 9 3/19/07 8:00:11 AM
3/19/07 8:00:7 AM 28 3/19/07 8:00:35 AM
3/19/07 8:01:7 AM 32 3/19/07 8:01:39 AM
3/19/07 8:01:13 AM 45 3/19/07 8:01:58 AM
3/19/07 8:02:36 AM 23 3/19/07 8:02:59 AM
3/19/07 8:05:53 AM 2 3/19/07 8:05:55 AM
3/19/07 8:10:33 AM 21 3/19/07 8:10:54 AM
3/19/07 8:12:14 AM 21 3/19/07 8:12:35 AM
3/19/07 8:13:5 AM 43 3/19/07 8:13:48 AM
3/19/07 8:14:13 AM 32 3/19/07 8:14:45 AM
3/19/07 8:14:17 AM 33 3/19/07 8:14:50 AM
3/19/07 8:14:21 AM 6 3/19/07 8:14:27 AM
3/19/07 8:14:21 AM 33 3/19/07 8:14:54 AM
3/19/07 8:14:29 AM 32 3/19/07 8:15:1 AM
3/19/07 8:14:39 AM 41 3/19/07 8:15:20 AM
3/19/07 8:15:30 AM 81 3/19/07 8:16:51 AM
3/19/07 8:18:21 AM 587 3/19/07 8:28:8 AM
3/19/07 8:18:55 AM 56 3/19/07 8:19:51 AM
3/19/07 8:20:44 AM 148 3/19/07 8:23:12 AM
3/19/07 8:20:52 AM 1002 3/19/07 8:37:34 AM
3/19/07 8:21:42 AM 23 3/19/07 8:22:5 AM
3/19/07 8:22:43 AM 33 3/19/07 8:23:16 AM
3/19/07 8:22:59 AM 855 3/19/07 8:37:14 AM
3/19/07 8:23:50 AM 1087 3/19/07 8:41:57 AM
3/19/07 8:25:58 AM 22 3/19/07 8:26:20 AM
3/19/07 8:27:57 AM 42 3/19/07 8:28:39 AM
3/19/07 8:28:53 AM 17 3/19/07 8:29:10 AM
3/19/07 8:29:8 AM 10 3/19/07 8:29:18 AM
3/19/07 8:30:1 AM 58 3/19/07 8:30:59 AM
3/19/07 8:30:27 AM 196 3/19/07 8:33:43 AM
3/19/07 8:31:49 AM 2 3/19/07 8:31:51 AM
3/19/07 8:31:56 AM 221 3/19/07 8:35:37 AM
3/19/07 8:32:16 AM 535 3/19/07 8:41:11 AM
3/19/07 8:32:26 AM 178 3/19/07 8:35:24 AM
3/19/07 8:32:35 AM 18 3/19/07 8:32:53 AM
3/19/07 8:32:47 AM 35 3/19/07 8:33:22 AM
3/19/07 8:33:5 AM 28 3/19/07 8:33:33 AM
3/19/07 8:33:11 AM 1 3/19/07 8:33:12 AM
3/19/07 8:33:19 AM 68 3/19/07 8:34:27 AM
3/19/07 8:34:11 AM 143 3/19/07 8:36:34 AM
3/19/07 8:34:17 AM 177 3/19/07 8:37:14 AM
3/19/07 8:36:20 AM 17 3/19/07 8:36:37 AM
3/19/07 8:36:26 AM 57 3/19/07 8:37:23 AM
3/19/07 8:36:39 AM 333 3/19/07 8:42:12 AM
3/19/07 8:38:22 AM 44 3/19/07 8:39:6 AM
3/19/07 8:39:0 AM 2 3/19/07 8:39:2 AM
3/19/07 8:40:4 AM 85 3/19/07 8:41:29 AM
3/19/07 8:40:28 AM 2 3/19/07 8:40:30 AM
3/19/07 8:41:3 AM 30 3/19/07 8:41:33 AM
3/19/07 8:42:14 AM 511 3/19/07 8:50:45 AM
3/19/07 8:42:22 AM 882 3/19/07 8:57:4 AM
3/19/07 8:42:24 AM 94 3/19/07 8:43:58 AM






This 'simple' problem is probably the most difficult issue I have
encountered. The issue is a short phone call ties up the line but then
releases it again - so getting totals has proved difficult.



In the end, I would like to be able to graph the results to visually
be able to tell how many times we have hit certain numbers.



I am hoping to merge several sites together via VOIP and need a way to
determine how many lines we actually need.



Good Luck and Thanks,



Nathaniel

On Saturday, March 29, 2008 8:09 AM
BarbReinhard wrote:

Calculate actual trunk utilization based on call start/duration
Let's say the Start data is in column A1:A63 and the END data is in Column
C1:C68.

Put this in D1 and copy down.

=SUMPRODUCT(--($A$1:$A$68=A1),--($C$1:$C$68<=C1))
--
HTH,
Barb Reinhardt

On Saturday, March 29, 2008 8:11 AM
BarbReinhard wrote:

Now that I think of it, this may not be exactly what you want.
Now that I think of it, this may not be exactly what you want. But it's
close.

You could set up another table with START/END time increments of 1 minute or
10 minutes, whatever you want and use a similar SUMPRODUCT calculation. If
you need help, let me know.
--
HTH,
Barb Reinhardt



" wrote:

On Saturday, March 29, 2008 8:22 AM
Joe wrote:

I haven't seen this problem in almost 30 years since I was going for my
I haven't seen this problem in almost 30 years since I was going for my
masters in computer science. I went to Steven's Institute of Technology
where many of the courses were taught by engineers from Bell Labs.

I'm using two worksheets. Sheet1 contains your input data. Not usre which
data is in which columns. I have the following

A: Date
B: Start Time
C: Phone Line
D: Date
E: End time

In sheet two the macro creates a row for each minute (0 to 1439) where there
are 1440 minutes in a day. The code then looks at each phone call and adds 1
to column B of sheet two for each minute the phone call was active. The code
then goes to each phone call and repeates the process.


The code is only set up for one day. You probably want to modifiy the code
that a diferent column in sheet 2 is used for each day. If you need
additional help let me know.



Sub getusage()

RowCount = 1

For MyMinute = 0 To 1439
With Sheets("Sheet2")
.Range("A" & RowCount) = _
TimeSerial(Int(MyMinute / 60), MyMinute Mod 60, 0)
RowCount = RowCount + 1
End With
Next MyMinute

RowCount = 1
With Sheets("Sheet1")
Do While .Range("A" & RowCount) < ""
StartTime = .Range("B" & RowCount)
EndTime = .Range("E" & RowCount)
MyTime = TimeSerial(Hour(StartTime), Minute(StartTime), 0)

With Sheets("Sheet2")
Set c = .Columns("A:A").Find(what:=MyTime, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
RowOffset = 0
Do While c.Offset(RowOffset, 0) <= EndTime
c.Offset(RowOffset, 1) = _
c.Offset(RowOffset, 1) + 1
RowOffset = RowOffset + 1
Loop
End If
End With
RowCount = RowCount + 1
Loop
End With



End Sub


" wrote:

On Saturday, March 29, 2008 8:33 AM
GarysStuden wrote:

This is a GREAT problem!
This is a GREAT problem! It falls in the area of Resource Utilization and
many thick books have been written on the topic. You can use Excel with some
simple formulas to get information about your data. Let???s say we split the
day up into 1 minute intervals and we want to know how many calls fell into
each interval.

I took your data and put the start date/time in column A, the duration in
column B, and the end date/time in column C. I began in row #3. In D1 and
D2 we enter:

3/19/2007 8:00:00
3/19/2007 8:01:00

the beginning and end of a typical one minute window. In D3 we enter:
=($D$2A3)*($D$1<C3) and copy down. We see in A1 thru D70:

3/19/2007 8:00:00
3/19/2007 8:01:00
3/19/2007 6:39:59 143 3/19/2007 6:42:22 0
3/19/2007 6:45:07 9 3/19/2007 6:45:16 0
3/19/2007 6:54:25 14 3/19/2007 6:54:39 0
3/19/2007 7:03:25 50 3/19/2007 7:04:15 0
3/19/2007 7:04:04 18 3/19/2007 7:04:22 0
3/19/2007 7:07:06 25 3/19/2007 7:07:31 0
3/19/2007 7:10:41 267 3/19/2007 7:15:08 0
3/19/2007 7:12:30 29 3/19/2007 7:12:59 0
3/19/2007 7:12:41 18 3/19/2007 7:12:59 0
3/19/2007 7:37:45 37 3/19/2007 7:38:22 0
3/19/2007 7:42:50 9 3/19/2007 7:42:59 0
3/19/2007 7:43:37 7 3/19/2007 7:43:44 0
3/19/2007 7:44:58 43 3/19/2007 7:45:41 0
3/19/2007 7:53:46 75 3/19/2007 7:55:01 0
3/19/2007 7:56:30 34 3/19/2007 7:57:04 0
3/19/2007 7:59:38 312 3/19/2007 8:04:50 1
3/19/2007 8:00:02 9 3/19/2007 8:00:11 1
3/19/2007 8:00:07 28 3/19/2007 8:00:35 1
3/19/2007 8:01:07 32 3/19/2007 8:01:39 0
3/19/2007 8:01:13 45 3/19/2007 8:01:58 0
3/19/2007 8:02:36 23 3/19/2007 8:02:59 0
3/19/2007 8:05:53 2 3/19/2007 8:05:55 0
3/19/2007 8:10:33 21 3/19/2007 8:10:54 0
3/19/2007 8:12:14 21 3/19/2007 8:12:35 0
3/19/2007 8:13:05 43 3/19/2007 8:13:48 0
3/19/2007 8:14:13 32 3/19/2007 8:14:45 0
3/19/2007 8:14:17 33 3/19/2007 8:14:50 0
3/19/2007 8:14:21 6 3/19/2007 8:14:27 0
3/19/2007 8:14:21 33 3/19/2007 8:14:54 0
3/19/2007 8:14:29 32 3/19/2007 8:15:01 0
3/19/2007 8:14:39 41 3/19/2007 8:15:20 0
3/19/2007 8:15:30 81 3/19/2007 8:16:51 0
3/19/2007 8:18:21 587 3/19/2007 8:28:08 0
3/19/2007 8:18:55 56 3/19/2007 8:19:51 0
3/19/2007 8:20:44 148 3/19/2007 8:23:12 0
3/19/2007 8:20:52 1002 3/19/2007 8:37:34 0
3/19/2007 8:21:42 23 3/19/2007 8:22:05 0
3/19/2007 8:22:43 33 3/19/2007 8:23:16 0
3/19/2007 8:22:59 855 3/19/2007 8:37:14 0
3/19/2007 8:23:50 1087 3/19/2007 8:41:57 0
3/19/2007 8:25:58 22 3/19/2007 8:26:20 0
3/19/2007 8:27:57 42 3/19/2007 8:28:39 0
3/19/2007 8:28:53 17 3/19/2007 8:29:10 0
3/19/2007 8:29:08 10 3/19/2007 8:29:18 0
3/19/2007 8:30:01 58 3/19/2007 8:30:59 0
3/19/2007 8:30:27 196 3/19/2007 8:33:43 0
3/19/2007 8:31:49 2 3/19/2007 8:31:51 0
3/19/2007 8:31:56 221 3/19/2007 8:35:37 0
3/19/2007 8:32:16 535 3/19/2007 8:41:11 0
3/19/2007 8:32:26 178 3/19/2007 8:35:24 0
3/19/2007 8:32:35 18 3/19/2007 8:32:53 0
3/19/2007 8:32:47 35 3/19/2007 8:33:22 0
3/19/2007 8:33:05 28 3/19/2007 8:33:33 0
3/19/2007 8:33:11 1 3/19/2007 8:33:12 0
3/19/2007 8:33:19 68 3/19/2007 8:34:27 0
3/19/2007 8:34:11 143 3/19/2007 8:36:34 0
3/19/2007 8:34:17 177 3/19/2007 8:37:14 0
3/19/2007 8:36:20 17 3/19/2007 8:36:37 0
3/19/2007 8:36:26 57 3/19/2007 8:37:23 0
3/19/2007 8:36:39 333 3/19/2007 8:42:12 0
3/19/2007 8:38:22 44 3/19/2007 8:39:06 0
3/19/2007 8:39:00 2 3/19/2007 8:39:02 0
3/19/2007 8:40:04 85 3/19/2007 8:41:29 0
3/19/2007 8:40:28 2 3/19/2007 8:40:30 0
3/19/2007 8:41:03 30 3/19/2007 8:41:33 0
3/19/2007 8:42:14 511 3/19/2007 8:50:45 0
3/19/2007 8:42:22 882 3/19/2007 8:57:04 0
3/19/2007 8:42:24 94 3/19/2007 8:43:58 0

This means that three calls fell into the window. Now we just continue with
column E for the next interval, etc. In the end we can create a
minute-by-minute histogram of phone usage.

But this is only the beginning. If the three calls were tiny, they may not
overlap at all, or they may overlap completely! I suggest you begin by
checking out:

http://www.google.com/search?hl=en&q...=Google+Search

to see if a solution has already been published.