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

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.  

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

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$2>A3)*($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=algorithm+resource+utilization&btnG=Google+
Search

to see if a solution has already been published.

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

Sorry for the late response.  I see the other responses have
calculated usage across fixed times.  This macro counts the calls
active at the beginning of a call.  It marks each call as Active, then
clears the flag as it ends.  To do that, it must keep track of the
greatest previous end time.  The data is pulled into an array for fast
processing.

The start and end times need to be in General format, so to retain the
original data, I copied the values to other columns manually.
Hopefully the comments help.  I haven't been able to ftp your large
file for testing with 2007, but the only question was whether the
array would go that large, and others checked that in previous
replies.

The spaces constant and debug.prints can be removed when you are happy
with the functionality.

Carl Hartness

Sub CountActiveCalls()
   ' Carl Hartness 3/31/08
   ' column A: start date & time in date and time format
   ' column B: duration in seconds
   ' column C: end date & time in date and time format
   '
   ' columns A, C are copied to E, F for general format
   ' column E: start date & time in general format
   ' column F: end date & time in general format
   ' column G: Active or blank
   ' column H: calls active at the beginning of this call
   '
   ' macro pulls columns E & F into array, adds 2 columns for
   '   temporary Active flag and count for that call

   Dim ary As Variant
   Dim x&, y&, cnt&        ' as long
   Dim greatestPrevEnd#    ' as double
   Const spaces$ = "                                   "

   With Range("E5")
       .CurrentRegion.NumberFormat = "General"

       ' load array
       ary = .CurrentRegion
Debug.Print "array size "; UBound(ary, 1), UBound(ary, 2)

       ' if needed, add columns 3 & 4 to array
       '   call active during this call
       '   calls active during this call
       If UBound(ary, 2) = 2 Then ReDim Preserve _
         ary(1 To UBound(ary, 1), 1 To UBound(ary, 2) + 2)

       greatestPrevEnd# = 0
       cnt& = 0
       For x& = 1 To UBound(ary, 1)
           cnt& = cnt& + 1

Debug.Print Left(spaces, 6 - Len(CStr(x&))) & x&; "    "; _
 Round(ary(x&, 2), 5) & Left(spaces, 13 - Len(CStr(Round(ary(x&, 2),
5)))); _
 Round(greatestPrevEnd#, 5) & Left(spaces, 13 -
Len(CStr(Round(greatestPrevEnd#, 5)))); _
 "+ "; cnt&

           ' advance greatest previous end time if needed
           If greatestPrevEnd# < ary(x&, 2) Then _
             greatestPrevEnd# = ary(x&, 2)

           ' this call always active during this call
           ary(x&, UBound(ary, 2) - 1) = "Active"

           ' decrement count for each active call ending before
           '   this call and before greatest previous end
           For y& = 1 To x& - 1
               If ary(y&, UBound(ary, 2) - 1) = "Active" _
                 And ary(y&, 2) < ary(x&, 1) _
                 And ary(y&, 2) < greatestPrevEnd# Then
                   ' call ends during this call
                   cnt& = cnt& - 1
                   ary(y&, UBound(ary, 2) - 1) = ""

Debug.Print Left(spaces, 9 - Len(CStr(y&))) & y&; " "; _
 Round(ary(y&, 2), 5) & Left(spaces, 13 - Len(CStr(Round(ary(y&, 2),
5)))); _
 Round(greatestPrevEnd#, 5) & Left(spaces, 13 -
Len(CStr(Round(greatestPrevEnd#, 5)))); _
 "- "; cnt&
               End If
           Next y&
           ary(x&, UBound(ary, 2)) = cnt&
       Next x&
       Range(.Cells, .Cells(UBound(ary, 1), UBound(ary, 2))) = ary
   End With
End Sub         ' CountActiveCalls

On Mar 29, 12:07 am, nathaniel.watk...@gmail.com wrote: