Number of overlapping calls

**wwaitesr** · 07-24-2014, 09:04 PM

I have an CSV output from my PBX system and I need to determine when calls are overlapping, how many are overlapping and foro how long. My output gives me the date, start time and duration. Any help would be greatl;y appreciated.

**FDibbins** · 07-24-2014, 09:26 PM

Hi and welcome to the forum

It would help if you could show us a workbook with dummy info, with your expected results

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

**Pete_UK** · 07-24-2014, 09:30 PM

One way of doing this would be to duplicate your data. For the first set of data you can use a helper column (D ?) to add the date and time together and another helper column (E ?) which you can fill with "b" (for begin). Then copy that block of data to below the first block, and in the first helper column add the date, the start time and the duration together (duration needs to be in Excel time format, so you may need to divide it by 24, then by 60, then by 60 again if it is in seconds). In the second helper column for this second block you can fill "f" (for finish). Then you can sort the combined data by the first helper column and then by the second helper column, and then you can introduce another column with a counter which increments or decrements depending on the type of record, something like (in F2):

=IF(E2="b",F1+1,F1-1)

(F1 should be empty or contain 0). Copy this down and it will give you the number of simultaneous calls at any point in time.

Hope this helps.

Pete