Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi there. I have a spreadsheet with thousands of phone call records. Each
has a start time, and end time and of course, therefore, a duration. I want to get excel to tell me how many concurrent calls there are. How do I do it ? Any help greatfully received. Charlie |
#2
![]() |
|||
|
|||
![]()
Hi Charlie,
Yes, Excel can definitely count concurrent events given start and end times. Here's how you can do it:
__________________
I am not human. I am an Excel Wizard |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
First question - of these thousands of phone calls, are they all on the same
day or are numerous days represented on the sheet? If more than one day is represented on the sheet, is the DATE of the call recorded as part of the start/end time of the call or somewhere else on the same row with the start end time? You're essentially asking Excel to take each date and compare it to all other dates and tell you how many others were going on at the same time - that's not going to work well if multiple days are involved unless you can distinguish the dates. It might be better if you set up a group of time ranges, down to whatever granularity you need and ask what calls were concurrent on a given day during those time periods. "Charlie B" wrote: Hi there. I have a spreadsheet with thousands of phone call records. Each has a start time, and end time and of course, therefore, a duration. I want to get excel to tell me how many concurrent calls there are. How do I do it ? Any help greatfully received. Charlie |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Yesterday I stared at this for about an hour trying to figure it out.
JLatham's point about your desired granularity is an important one, since it is an important aspect of your solution. I made some assumptions about "down to the minute" granularity and tried to develop a way to determine calls originating during a particular minute and calls that were ongoing during a particular minute, and couldn't get the logic down, and then couldn't devote more time to it. The best I can figure right now is to calculate calls in progress during a particular time frame by listing for each call the start and end time; use your desired time granularity as headers across columns; then use IF statements to determine if a call is in progress during a given time period. If TRUE then return 1 in a given cell, and at the bottom of the column sum all the 1s. That's not very elegant, tho, hopefully one of the resident geniuses can respond with a better method. |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Assuming you have call data with start date/time and end date/time
(i.e. start date/time + duration), one way of doing this is to create TWO records for every call record. One record to contain START date/ time and an indicator set to "S", and the second to contain END date/ time and an indicator set to "E". Merge the two sets of records together and sort by date/time in ascending sequence. If the S/E indicator is in column C, and assuming first record is in row 2, then in column D row 2 add the formula: =IF(C2="S",D1+1,D1-1) and replicate this formula downwards to all other records (cell D1 should be initialised to 0). Column D should then contain the number of simultaneous calls at any one time. You can easily determine the maximum number (using the MAX function) AND the number of times that this number, and every lower number, was reached (using the COUNTIF function). Hope this helps. Pete On Sep 17, 5:56 pm, Charlie B <Charlie wrote: Hi there. I have a spreadsheet with thousands of phone call records. Each has a start time, and end time and of course, therefore, a duration. I want to get excel to tell me how many concurrent calls there are. How do I do it ? Any help greatfully received. Charlie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How do I count the number of events in a 90 day period? | Excel Worksheet Functions | |||
How many concurrent nested IF statments does Excel allow? | Excel Discussion (Misc queries) | |||
use Now() in two cells for start and finish times | Excel Worksheet Functions | |||
Finding concurrent events from a list with a start and an end time | Excel Discussion (Misc queries) | |||
Countif("between start/end times) | Excel Discussion (Misc queries) |