Overlap times formula/graph

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PauloG - 18 May 2007 09:53 GMT

Hi,

I work in a small office with 4 people and need to produce a graph which
shows when we have more than 1 person on the phone at the same time. The
spreadsheet shows when calls started, the time on the call and when the call
ended. It also shows when times overlap each other (sample below).

Date    Start    Mins    End    Overlap
14/07    11:14:19    142.52    13:37:11    Y
14/07    13:24:20    13.87    13:38:47    Y
14/07    16:09:28    327.28    21:36:56    Y
14/07    17:01:25    14.07    17:15:32    Y
14/07    20:51:17    95.22    22:26:39    Y
14/07    20:51:37    96.03    22:27:40    Y
14/07    21:37:42    15.47    21:53:29    Y
14/07    22:11:54    24.45    22:36:39    Y
14/07    22:20:48    68.45    23:29:33    Y
14/07    22:27:42    3.12    22:30:54    Y

Obviously there will be a variety of combinations when people come off the
phone so this frees up another person. I could go through this manually but
if there is a formula to be able to do this it would be helpful.

I then need to show this information with the date on the x axis and the
amount of people on the phone at the same time on the y axis.

Any help would be great.

Thanks

Paul

Reply to this Message

loudfish - 18 May 2007 18:46 GMT

> Hi,
>
[quoted text clipped - 3 lines]
> I then need to show this information with the date on the x axis and the
> amount of people on the phone at the same time on the y axis.

Your question is vague, and I'm not sure if you need to know:

a. the maximum number of people on the phone at once at any time
during each day
b. the number of people on the phone at a time, during the day.

I'm assuming the second one, which I think is more useful.

>From your data, decide how often you want to check how many people are

on the phone at once, and put all these times down in a worksheet.

e.g.

10:00:00
10:05:00
10:10:00
...

against each one, use a formula like this:

=SUMPRODUCT(($D$15:$D$22<C25)*1,($E$15:$E$22>C25)*1)

where.............
C25 is the time you are checking e.g. 10:00:00
D15:D22 is the list of start times
E15:E22 is the list of end times

This will give you a count of how many people were on the phone at
that time. Actually, how many calls were in progress (ie started, but
not finished) at that time.

Copy the formula to cover each of your "sample" times.

Build a graph from these sample sets.

HTH

Andrew

Reply to this Message