Overlap times formula/graph

[Guest]

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

 

[loudfish]

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

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