Time Question

[Guest]

Hi,

I hope that someone can help?

I have a worksheet that contains multiple conferences that have a start
time and end time all with varying durations. some conferences will run
simultaneously and I need to show the number of conferences that are running
simultaneously on a per minute basis over a 24 hour period. The data that i
have is for 12 months and contains about 13,000 conferences over a 12 month
period

Is there any functionality to provide this information?

Many thanks in anticipation

Rgds

Colin

 

[Guest]

Yes, this can be done. You'll get a better response if you provide more
information about how your data is organized and how you'd like it presented,
but I'll make some assumptions and take a stab.
Suppose your data on calls is organized one row per call, with a the date in
column A, start time in column B, end time in column C, all on Sheet1, rows
2:13001. Your job will be to create a summary on Sheet2, with the Date set
in A1, then the minutes in A2:A1441 and the count of calls in B2:B1441.
You can create the formula to count the calls in Sheet2!B2 using the
sumproduct function. The formula would look something like
=sumproduct(--(Sheet1!A$2:A$13001=$A$1),--(Sheet1!B$2:B$13001<=A2),--(Sheet1!C$2:C$13001>A2)).
The formula basically counts up the number of calls on the given date, begun
at or before the time of interest and ended after the time of interest. I
think I've got the cell references set so that you can copy that formula into
B3:B1441 to get the count for each minute of the date specified.
--Bruce