Site to Site concurrent call calculation

I am trying to find a way to calcluate concurrent calls with three criteria, Start Date, End Date, and Destination. Start and End Date format is MM/DD/YYY hh:mm:ss and the destination is currently a general text field e.g. MN013

I cannot seem to find a way to include the 3rd (destination) criteria.

The formula to find overlapping times (=IF(DAY(B73)=DAY(A74),COUNTIF(B$2:B73,">="&A73)+COUNTIF(A74:A$63338,"<"&B73),"EOD") works great but in this case, concurrent calls must also be to the same destination (mail route). In the example below, only the calls with MN013 at the end of the row are actually concurrent with each other.

7/19/2010 15:19:25 7/19/2010 15:32:30 0:13:05 4 908696 NJ006
7/19/2010 15:29:52 7/19/2010 15:31:51 0:01:59 2 609301 NJ002
7/19/2010 15:31:51 7/19/2010 15:31:53 0:00:02 3 609301 NJ002
7/19/2010 15:41:13 7/19/2010 15:44:05 0:02:52 1 603629 NH020
7/19/2010 15:53:45 7/19/2010 16:25:40 0:31:55 10 763744 MN013
7/19/2010 15:59:16 7/19/2010 15:59:18 0:00:02 2 131518 MN008
7/19/2010 15:59:33 7/19/2010 16:26:35 0:27:02 9 952833 MN002
7/19/2010 16:02:25 7/19/2010 16:02:30 0:00:05 3 952936 MN008
7/19/2010 16:02:54 7/19/2010 16:38:46 0:35:52 11 763744 MN013
7/19/2010 16:15:04 7/19/2010 16:15:09 0:00:05 4 952992 MN012
7/19/2010 16:17:41 7/19/2010 16:17:45 0:00:04 4 713296 TX035
7/19/2010 16:21:59 7/19/2010 16:22:04 0:00:05 4 304766 WV050
7/19/2010 16:23:31 7/19/2010 16:28:34 0:05:03 5 304766 WV050
7/19/2010 16:25:25 7/19/2010 16:33:39 0:08:14 7 952931 MN006
7/19/2010 16:33:19 7/19/2010 16:35:07 0:01:48 5 215902 PA062
7/19/2010 16:33:38 7/19/2010 16:38:52 0:05:14 5 763553 MN013
7/19/2010 16:34:04 7/19/2010 16:43:10 0:09:06 4 952512 MN030
7/19/2010 16:43:56 7/19/2010 16:46:06 0:02:10 1 304766 WV050
7/19/2010 16:53:34 7/19/2010 16:53:56 0:00:22 1 443896 MD018
7/19/2010 16:54:16 7/19/2010 16:54:57 0:00:41 1 952931 MN006
7/19/2010 17:06:13 7/19/2010 18:10:04 1:03:51 3 952992 MN012

I have used your data set in A2:H22

Sorted the data by location, start time, end time

In a helper cell column I

=AND(H2=H3)*(D2>B3)

This looks for the same site and if the end time is less than the start time for the next entry

Initial test seems to work

edvwvw

Originally Posted by edvwvw

I have used your data set in A2:H22

Sorted the data by location, start time, end time

In a helper cell column I

=AND(H2=H3)*(D2>B3)

This looks for the same site and if the end time is less than the start time for the next entry

Initial test seems to work

edvwvw

Hi and Thanks, I appreciate the help! I can't seem to get this to work just yet and wanted to make a clarification. I gathered from your response that you had put the date and times in their own columns (I should have included headers for columns A-F). You did point out a good approach with the 3 way sort. I added rows for reference as well. The end result should end up showing that MN013 (rows 6,10, and 17) had 3 concurrent calls.

1) Start Date/Time End Date/Time Duration Counter Prefix Site
2) 7/19/2010 15:19:25 7/19/2010 15:32:30 0:13:05 3 908696 NJ006
3) 7/19/2010 15:29:52 7/19/2010 15:31:51 0:01:59 2 609301 NJ002
4) 7/19/2010 15:31:51 7/19/2010 15:31:53 0:00:02 3 609301 NJ002
5) 7/19/2010 15:41:13 7/19/2010 15:44:05 0:02:52 1 603629 NH020
6) 7/19/2010 15:53:45 7/19/2010 16:25:40 0:31:55 10 763744 MN013
7) 7/19/2010 15:59:16 7/19/2010 15:59:18 0:00:02 2 131518 MN008
8) 7/19/2010 15:59:33 7/19/2010 16:26:35 0:27:02 9 952833 MN002
9) 7/19/2010 16:02:25 7/19/2010 16:02:30 0:00:05 3 952936 MN008
10) 7/19/2010 16:02:54 7/19/2010 16:38:46 0:35:52 11 763744 MN013
11) 7/19/2010 16:15:04 7/19/2010 16:15:09 0:00:05 4 952992 MN012
12) 7/19/2010 16:17:41 7/19/2010 16:17:45 0:00:04 4 713296 TX035
13) 7/19/2010 16:21:59 7/19/2010 16:22:04 0:00:05 4 304766 WV050
14) 7/19/2010 16:23:31 7/19/2010 16:28:34 0:05:03 5 304766 WV050
15) 7/19/2010 16:25:25 7/19/2010 16:33:39 0:08:14 7 952931 MN006
16) 7/19/2010 16:33:19 7/19/2010 16:35:07 0:01:48 5 215902 PA062
17) 7/19/2010 16:33:38 7/19/2010 16:38:52 0:05:14 5 763553 MN013
18) 7/19/2010 16:34:04 7/19/2010 16:43:10 0:09:06 4 952512 MN030
19) 7/19/2010 16:43:56 7/19/2010 16:46:06 0:02:10 1 304766 WV050
20) 7/19/2010 16:53:34 7/19/2010 16:53:56 0:00:22 1 443896 MD018
21) 7/19/2010 16:54:16 7/19/2010 16:54:57 0:00:41 1 952931 MN006
22) 7/19/2010 17:06:13 7/19/2010 18:10:04 1:03:51 EOD 952992 MN012

Sorry but I can't seem to make this work. The Start Date/Time are both column A, the End Date/Time are both in Column B, the Duration is in Column C, Counter is in Column D, Prefix in Column E, site code is in Column F. However, even if I break the date and time into separate columns as I think you are doing (so the counter column is column F), the concurrent call count does not work. I put your formula in the Counter column (F) because if creates a circular reference if I put it in column I.

Concurrent calls in this case aere those which occur on the same day, to the same site, with overlapping times.

I've attached a snippet of the spreadsheet to make sure we are in sync.