I have a spread sheet that lists the date and time of an event in the separate cell (mm/dd/yy h:mm). I need to find a way to count the simultaneous calls. Then it will output the maximum number of simultaneous calls based from given data regardless of number of row .
So for this example I should have an output of 4 as value for SIMULTANEOUS_CALLS
since calls for coming form caller 6-9 p happened at the same time at 1/1/2016 8:14 to 1/1/2016 8:17 even though they have different start time and end time.
and it repeated twice ........
In addition I should also have an output of 2 as value for OCCURRENCE
since calls for coming form caller 10-13 happened at the same time at 1/1/2016 8:44 to 1/1/2016 8:46 even though they have different start time and end time.
So for this example ,and based from observation we have a clue to get the the time range where calls occur simultaneously.Which is the maximum start time and minimum end time.
Any help would be greatly appreciated. I am completely stuck. Below is a sample data and the initial formula but it only count the number of calls between 8am to 9am.
=COUNTIFS(B1:B13,">=8:00",B1:B13,"<=9:00")
Caller Number start_time end_time
1 1/1/2016 8:00 1/1/2016 8:04 -- calls from caller 1-3 are happening at the same time
2 1/1/2016 8:02 1/1/2016 8:07 -- calls from caller 1-3 are happening at the same time
3 1/1/2016 8:03 1/1/2016 8:05 -- calls from caller 1-3 are happening at the same time
4 1/1/2016 8:02 1/1/2016 8:03 -- calls from caller 4-5 are happening at the same time
5 1/1/2016 8:04 1/1/2016 8:05 -- calls from caller 4-5 are happening at the same time
6 1/1/2016 8:11 1/1/2016 8:19 -- calls from caller 6 -9 are happening at the same time
7 1/1/2016 8:12 1/1/2016 8:18 -- calls from caller 6 -9 are happening at the same time
8 1/1/2016 8:13 1/1/2016 8:17 -- calls from caller 6 -9 are happening at the same time
9 1/1/2016 8:14 1/1/2016 8:20 -- calls from caller 6 -9 are happening at the same time
10 1/1/2016 8:37 1/1/2016 8:49 -- calls from caller 10-13 are happening at the same time
11 1/1/2016 8:40 1/1/2016 8:47 -- calls from caller 10-13 are happening at the same time
12 1/1/2016 8:41 1/1/2016 8:46 -- calls from caller 10-13 are happening at the same time
13 1/1/2016 8:44 1/1/2016 8:48 -- calls from caller 10-13 are happening at the same time
So for this example I should have an output of 4 as value for SIMULTANEOUS_CALLS
since calls for coming form caller 6-9 p happened at the same time at 1/1/2016 8:14 to 1/1/2016 8:17 even though they have different start time and end time.
and it repeated twice ........
In addition I should also have an output of 2 as value for OCCURRENCE
since calls for coming form caller 10-13 happened at the same time at 1/1/2016 8:44 to 1/1/2016 8:46 even though they have different start time and end time.
So for this example ,and based from observation we have a clue to get the the time range where calls occur simultaneously.Which is the maximum start time and minimum end time.
Any help would be greatly appreciated. I am completely stuck. Below is a sample data and the initial formula but it only count the number of calls between 8am to 9am.
=COUNTIFS(B1:B13,">=8:00",B1:B13,"<=9:00")
Caller Number start_time end_time
1 1/1/2016 8:00 1/1/2016 8:04 -- calls from caller 1-3 are happening at the same time
2 1/1/2016 8:02 1/1/2016 8:07 -- calls from caller 1-3 are happening at the same time
3 1/1/2016 8:03 1/1/2016 8:05 -- calls from caller 1-3 are happening at the same time
4 1/1/2016 8:02 1/1/2016 8:03 -- calls from caller 4-5 are happening at the same time
5 1/1/2016 8:04 1/1/2016 8:05 -- calls from caller 4-5 are happening at the same time
6 1/1/2016 8:11 1/1/2016 8:19 -- calls from caller 6 -9 are happening at the same time
7 1/1/2016 8:12 1/1/2016 8:18 -- calls from caller 6 -9 are happening at the same time
8 1/1/2016 8:13 1/1/2016 8:17 -- calls from caller 6 -9 are happening at the same time
9 1/1/2016 8:14 1/1/2016 8:20 -- calls from caller 6 -9 are happening at the same time
10 1/1/2016 8:37 1/1/2016 8:49 -- calls from caller 10-13 are happening at the same time
11 1/1/2016 8:40 1/1/2016 8:47 -- calls from caller 10-13 are happening at the same time
12 1/1/2016 8:41 1/1/2016 8:46 -- calls from caller 10-13 are happening at the same time
13 1/1/2016 8:44 1/1/2016 8:48 -- calls from caller 10-13 are happening at the same time
