Time Range Count

anhelly

New Member
Joined
Aug 31, 2016
Messages
2
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
 

Some videos you may like

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".

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,179
Try to state the output counts (outcome) you desire from your sample:

Row\Col
A​
B​
C​
1​
Caller Numberstart_timeend_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
Joined
Aug 31, 2016
Messages
2
Try to state the output counts (outcome) you desire from your sample:

Row\Col
A​
B​
C​
1​
Caller Numberstart_timeend_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
 

Watch MrExcel Video

Forum statistics

Threads
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...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top