Working out concurrent usage in Excel 1

[DancingDave]

I am trying to calculate the peak number of concurrent calls into our voicemail system.

I have the following information for the calls

Start time (Column B)
Stop Time (Column C)
Duration (Column D)

My approach has been to define short time periods (e.g. 08:00:00 to 08:00:59, 08:01:00 to 08:01:59 etc. etc. in Columns I and J) and try calculate how many calls are ongoing in that period.

B C D
1 08:00:29 08:00:39 00:00:10
2 08:00:34 08:01:22 00:00:48
3 08:01:37 08:01:55 00:00:18
4 08:00:01 08:02:48 00:03:00
5 07:59:11 08:01:26 00:02:15

I J
1 08:00:00 08:00:59
2 08:01:00 08:01:59
3 08:02:00 08:02:59
4 08:03:00 08:03:59

I have summed the result of the two following formulae

=COUNTIF($B$2:$B$5,">="&H2)-COUNTIF($B$2:$B$5,">="&I2)
(how many calls start between e.g. 08:00:00 and 08:00:59)

=COUNTIF($C$2:$C$5,">="&H2)-COUNTIF($C$2:$C$5,">="&I2)
(how many calls end between e.g. 08:00:00 and 08:00:59)

This works okay, but it doesn't capture calls that span more than two minutes e.g. row 4, which goes from 08:00:01 to 08:02:48 registers in the 08:00:00 to 08:00:59 timeslot and the 08:02:00 to 08:02:59 timeslot, but NOT the 08:01:00 to 08:01:59 timeslot.

We have quite a few long calls so I need to understand how to include these figures.

Any suggestions or is there a better way to do this

Thanks in advance

DD

ps my data set is several hundred rows, so doing it manually isn't an option

 

[GlennUK]

The formula to count how many calls overlapped a time slot at all would be:

=SUMPRODUCT(--($C$2:$C$6>=H2)*--($B$2:$B$6<=I2))

wouldn't it?

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[DancingDave]

It would certainly appear so. Many thanks and have a star!

DD

 

[GlennUK]

I'm glad that worked for you! ( and thanks for the star )

Cheers, Glenn.

Beauty is in the eye of the beerholder.