code for overlaping time

[farid2001]

Hello

What I need is a code that could tell me what is the maximum amount of
simultaneous phone calls for a 24 hour period, example:

call # Call started Duration Call ended
6 03/03/2009 09:44:57 00:00:40 09:45:37
5 03/03/2009 09:38:32 00:00:22 09:38:54
4 03/03/2009 09:37:13 00:02:46 09:39:59
3 03/03/2009 09:27:29 00:00:49 09:28:18
2 03/03/2009 09:26:29 00:00:31 09:27:00
1 03/03/2009 09:24:00 00:17:04 09:41:04

In this case, the maximum # of simultaneous calls is 3, since call #1
overlaps with call #4 which also overlaps with call #5

Is it possible to get such a code?

Your help will be appreciated

Thanks & regards
farid2001

 

[Joel]

This is a problem that mathematicans have explored for 100 years. It is a
class project that many teacher assigned. The solution involves making that
divides a 24 hour time period into equal time periods like 1 minute, 2
minutes, 5 minutes, 10 minutes, 15 minutes. Lets say we use 5 minutes then
your worksheet will look like this

Column A

12:00 AM
12:05 AM
12:10 AM
12:15 AM

skip time - contiue like this until midnight

11:40 PM
11:45 PM
11:50 PM

11:55 PM


Next take each of your phone call and add 1 to column B for each time period
in the table above when they occur. A phone call can be placed in more than
one entry in the table. The time period with the largest number in column B
is the answer.

 

[Nigel]

You need to decide the precision of your model, that is the smallest time
interval you wish to use when determining overlapping times. In this case
if you used seconds you need to count occurrence into 86400 intervals (24
hours * 3600 per hour), that is a lot of computation time and could be long
depending on how many calls you are analysing. But maybe that is too
precise, minute intervals would reduce this to 1440.

What precision do you require and how many calls are you typically
analysing?

 

[Robert McCurdy]

This can be done without code, or adapt this to a coded solution.

Sort with the time the calls are made.
Lets say this is in A column, and I've named this range pc.
Where C2 is the start of the Call Ended time.

=LOOKUP(C2,pc,ROW(pc))-ROW()

and copy it down the list - I used D column for the above formula.

This returns the max number of overlapping calls.
To find the actual phone call time from column 'A' you could use this in
cell F2:
(D2313 holds the above lookup formula)

=ADDRESS(MATCH(MAX(D2313),D2313,0)+1,1,4)

And provide it with a range name... say Overlap like this:

=INDIRECT(Sheet1!$F$2)

So now all you need in code to return these values:

MsgBox "The maximum call overlapping is " & _
[Overlap].Offset(0, 3) & ", and" & vbCr & _
"the phone call was made at " & _
Format([Overlap], "h:mm:ss am/pm") & "."

Of course there may be several calls that have the same number of overlap,
so you may still need to check for each which is the larger duration.

Regards
Robert McCurdy

 

[farid2001]

Hello Nigel

9 am to 9 pm
About 1400 calls
1 minute intervals

Thanks for your prompt response.

Regards
farid2001

 

[Herbert Seidenberg]

Excel 2007
With Gantt Chart.
Timed overlap.
No code.
http://www.mediafire.com/file/y1oljfgolmj/03_11_09b.xlsx

 

[farid2001]

Herbert

Absolutely fantastic!!!
On a scale from 1 to 10 you deserve 11
It works to perfection.

Thank you very much for your help.

Regards
farid2001