# Time Range Count

#### anhelly

##### New Member
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

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

##### MrExcel MVP
Try to state the output counts (outcome) you desire from your sample:

 Row\Col A​ B​ C​ 1​ Caller Number start_time end_time 2​ 1​ 1/1/2016 8:00​ 1/1/2016 8:04​ 3​ 2​ 1/1/2016 8:02​ 1/1/2016 8:07​ 4​ 3​ 1/1/2016 8:03​ 1/1/2016 8:05​ 5​ ​ 6​ ​ 7​ 4​ 1/1/2016 8:02​ 1/1/2016 8:03​ 8​ 5​ 1/1/2016 8:04​ 1/1/2016 8:05​ 9​ ​ 10​ ​ 11​ 6​ 1/1/2016 8:11​ 1/1/2016 8:19​ 12​ 7​ 1/1/2016 8:12​ 1/1/2016 8:18​ 13​ 8​ 1/1/2016 8:13​ 1/1/2016 8:17​ 14​ 9​ 1/1/2016 8:14​ 1/1/2016 8:20​ 15​ ​ 16​ 1​ 1/1/2016 8:03​ 1/1/2016 8:04​ 17​ 1​ 1/1/2016 8:04​ 1/1/2016 8:04​ 18​ 1​ 1/1/2016 8:04​ 1/1/2016 8:04​ 19​ 1​ 1/1/2016 8:04​ 1/1/2016 8:04​

#### anhelly

##### New Member
Try to state the output counts (outcome) you desire from your sample:

 Row\Col A​ B​ C​ 1​ Caller Number start_time end_time 2​ 1​ 1/1/2016 8:00​ 1/1/2016 8:04​ 3​ 2​ 1/1/2016 8:02​ 1/1/2016 8:07​ 4​ 3​ 1/1/2016 8:03​ 1/1/2016 8:05​ 5​ 6​ 7​ 4​ 1/1/2016 8:02​ 1/1/2016 8:03​ 8​ 5​ 1/1/2016 8:01​ 1/1/2016 8:03​ 9​ 10​ 11​ 6​ 1/1/2016 8:11​ 1/1/2016 8:19​ 12​ 7​ 1/1/2016 8:12​ 1/1/2016 8:18​ 13​ 8​ 1/1/2016 8:13​ 1/1/2016 8:17​ 14​ 9​ 1/1/2016 8:14​ 1/1/2016 8:20​ 15​ 16​ 1​ 1/1/2016 8:03​ 1/1/2016 8:04​ 17​ 1​ 1/1/2016 8:04​ 1/1/2016 8:04​ 18​ 1​ 1/1/2016 8:04​ 1/1/2016 8:04​ 19​ 1​ 1/1/2016 8:04​ 1/1/2016 8:04​

<tbody>
</tbody>

There are two answer that must appear in another column for example in column E it should say SIMULTANEOUS_CALLS=4 OCCURRENCE=2 something like that.
as you may observe as an initial comparison which is caller 1 and 2

caller 1 -2 they met bet 1/1/2016 8:02 and 1/1/2016 8:04 it will Output SIMULTANEOUS_CALLS=2 (then adding the data for caller 3)
caller 1 -3 they met bet 1/1/2016 8:03 and 1/1/2016 8:04 it will Output SIMULTANEOUS_CALLS=3 (then adding the data for caller 4)
caller 1 -4 they met bet 1/1/2016 8:02 and 1/1/2016 8:03 it will still Output SIMULTANEOUS_CALLS=3

This is because caller number 4 happened between 1/1/2016 8:02 and 1/1/2016 8:03 then adding data for caller 5

caller 1 -5 they met bet 1/1/2016 8:03 and 1/1/2016 8:04 it will still Output SIMULTANEOUS_CALLS=3 ls since caller number 5 happened
between 1/1/2016 8:01 and 1/1/2016 8:03.

This process will continue until the end of data in column.In this case it will super cede the previous output for SIMULTANEOUS_CALLS=
3 since calls from callers 6-9 simultaneously happened between 1/1/2016 8:14 and 1/1/2016 8:17

1,102,274
Messages
5,485,782
Members
407,515
Latest member
franjey

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...