Use excel to count number of concurrent external calls

I am contemplating switching my company phone service from a traditional pri circuit to a virtual sip trunk. I need to determine the maximum number of calls at any one time. I have a spreadsheet (see attached) that contains start_timestamp, answer_timestamp, end_timestamp, destination, billsec, duration, and direction. The time stamps are down to the second. I'd like to calculate how many simultaneous calls are happening at any given time so I can order the correct amount of telephone lines from the phone company. To complicate things, i do not need to count calls that are between internal 4 digit extensions. For example, i do not need to count a call between 3876 and 3954. I only need to count a call if either the caller or destination is an external number. Ive googled around but cant find anything that does specifically this. Thank you!!!cdr.xls

One way of doing this (probably better in another sheet), is to duplicate all the records - you don't need all the fields, just the timestamps i.e. copy the start_timestamp field for all the records into cell A2 and use a helper column (B) and fill it with "b" (for begin), then copy all the end_timestamp column below it in column A and use "e" (for end) in column B for those records. You can use row 1 for headers - Date/time and Type. Then you can sort this data by Date/time and by Type, and then in column C you can put these in the cells stated:

C1: Number
C2: 1
C3: =IF(B3="b",C2+1,C2-1)

and then copy the formula from C3 all the way down to the bottom of your data - that will give you a running count of the number of simultaneous calls at any point in time, by incrementing the count when a call begins and decrementing the count when a call finishes. You can apply a filter to that column to see the range of values, or you could use a formula like = MAX(C:C) to find the largest value.

Hope this helps.

Pete