number of concurrent calls

T

tagteam3283

New Member
Joined
Jun 12, 2018
Messages
6
hi guys,

New to the forum. However been referring to this forum from general google searches for years now.

Seems today im at an impasse for a new problems which is quite specific. I've no doubt heads will be scratching.

I need to be a manner in which I can determine the number of concurrent calls ongoing when a new call is initiated: concurrent - how many active calls are ongoing:

hopefully you can make sense of this example data. I would like to auto-calc the last column instead of visually checking how many calls are active

[TABLE="width: 576"]
<tbody>[TR]
[TD]Start Date
[/TD]
[TD]Start Time
[/TD]
[TD]Duration
[/TD]
[TD]End Time [Start Time+Duration]
[/TD]
[TD]Manual Calc - Active calls
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]16:18:39
[/TD]
[TD]00:00:04
[/TD]
[TD]16:18:43
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]16:52:10
[/TD]
[TD]00:00:05
[/TD]
[TD]16:52:15
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]16:55:25
[/TD]
[TD]00:00:02
[/TD]
[TD]16:55:27
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]17:07:07
[/TD]
[TD]00:00:04
[/TD]
[TD]17:07:11
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]17:18:11
[/TD]
[TD]00:04:17
[/TD]
[TD]17:22:28
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]17:18:46
[/TD]
[TD]00:09:48
[/TD]
[TD]17:28:34
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]17:19:07
[/TD]
[TD]00:09:47
[/TD]
[TD]17:28:54
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]17:26:16
[/TD]
[TD]00:01:40
[/TD]
[TD]17:27:56
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]17:30:49
[/TD]
[TD]00:00:15
[/TD]
[TD]17:31:04
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]17:31:59
[/TD]
[TD]00:00:12
[/TD]
[TD]17:32:11
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]17:33:06
[/TD]
[TD]00:03:45
[/TD]
[TD]17:36:51
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]17:44:19
[/TD]
[TD]00:03:53
[/TD]
[TD]17:48:12
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]17:48:22
[/TD]
[TD]00:03:52
[/TD]
[TD]17:52:14
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]17:48:22
[/TD]
[TD]00:05:03
[/TD]
[TD]17:53:25
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]17:48:22
[/TD]
[TD]00:03:54
[/TD]
[TD]17:52:16
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]17:48:22
[/TD]
[TD]00:04:21
[/TD]
[TD]17:52:43
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]17:48:29
[/TD]
[TD]00:09:48
[/TD]
[TD]17:58:17
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]17:49:14
[/TD]
[TD]00:09:48
[/TD]
[TD]17:59:02
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]17:49:15
[/TD]
[TD]00:09:49
[/TD]
[TD]17:59:04
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]17:54:30
[/TD]
[TD]00:08:14
[/TD]
[TD]18:02:44
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]17:54:30
[/TD]
[TD]00:08:12
[/TD]
[TD]18:02:42
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]17:54:30
[/TD]
[TD]00:08:13
[/TD]
[TD]18:02:43
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]17:54:31
[/TD]
[TD]00:08:14
[/TD]
[TD]18:02:45
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]18:00:45
[/TD]
[TD]00:08:02
[/TD]
[TD]18:08:47
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]18:00:46
[/TD]
[TD]00:08:03
[/TD]
[TD]18:08:49
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]18:00:46
[/TD]
[TD]00:08:02
[/TD]
[TD]18:08:48
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]18:03:08
[/TD]
[TD]00:05:43
[/TD]
[TD]18:08:51
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]18:03:08
[/TD]
[TD]00:05:45
[/TD]
[TD]18:08:53
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]18:03:08
[/TD]
[TD]00:05:44
[/TD]
[TD]18:08:52
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]18:03:09
[/TD]
[TD]00:05:45
[/TD]
[TD]18:08:54
[/TD]
[TD]7
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]18:09:16
[/TD]
[TD]00:05:40
[/TD]
[TD]18:14:56
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]18:09:16
[/TD]
[TD]00:05:35
[/TD]
[TD]18:14:51
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]18:09:16
[/TD]
[TD]00:02:25
[/TD]
[TD]18:11:41
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]18:09:16
[/TD]
[TD]00:05:36
[/TD]
[TD]18:14:52
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]18:09:17
[/TD]
[TD]00:05:37
[/TD]
[TD]18:14:54
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]18:09:17
[/TD]
[TD]00:05:41
[/TD]
[TD]18:14:58
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]04/03/2017
[/TD]
[TD]18:09:21
[/TD]
[TD]00:05:38
[/TD]
[TD]18:14:59
[/TD]
[TD]7
[/TD]
[/TR]
</tbody>[/TABLE]

thanks in advance
tagteam
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Welcome to Mr Excel forum

Maybe this...

Formula in E2 copied down
=COUNTIFS(A$2:A2,A2,D$2:D2,">="&B2)

Hope this helps

M.
 
Upvote 0
Slight different but similar:


Book1
ABCDE
1Start DateStart TimeDurationEnd Time [Start Time+Duration]Manual Calc - Active calls
204/03/201716:18:3900:00:0416:18:431
304/03/201716:52:1000:00:0516:52:151
404/03/201716:55:2500:00:0216:55:271
504/03/201717:07:0700:00:0417:07:111
604/03/201717:18:1100:04:1717:22:281
704/03/201717:18:4600:09:4817:28:342
804/03/201717:19:0700:09:4717:28:543
904/03/201717:26:1600:01:4017:27:563
1004/03/201717:30:4900:00:1517:31:041
1104/03/201717:31:5900:00:1217:32:111
1204/03/201717:33:0600:03:4517:36:511
1304/03/201717:44:1900:03:5317:48:121
1404/03/201717:48:2200:03:5217:52:141
1504/03/201717:48:2200:05:0317:53:252
1604/03/201717:48:2200:03:5417:52:163
1704/03/201717:48:2200:04:2117:52:434
1804/03/201717:48:2900:09:4817:58:175
1904/03/201717:49:1400:09:4817:59:026
2004/03/201717:49:1500:09:4917:59:047
2104/03/201717:54:3000:08:1418:02:444
2204/03/201717:54:3000:08:1218:02:425
2304/03/201717:54:3000:08:1318:02:436
2404/03/201717:54:3100:08:1418:02:457
2504/03/201718:00:4500:08:0218:08:475
2604/03/201718:00:4600:08:0318:08:496
2704/03/201718:00:4600:08:0218:08:487
2804/03/201718:03:0800:05:4318:08:514
2904/03/201718:03:0800:05:4518:08:535
3004/03/201718:03:0800:05:4418:08:526
3104/03/201718:03:0900:05:4518:08:547
3204/03/201718:09:1600:05:4018:14:561
3304/03/201718:09:1600:05:3518:14:512
3404/03/201718:09:1600:02:2518:11:413
3504/03/201718:09:1600:05:3618:14:524
3604/03/201718:09:1700:05:3718:14:545
3704/03/201718:09:1700:05:4118:14:586
3804/03/201718:09:2100:05:3818:14:597
Sheet1
Cell Formulas
RangeFormula
E2=COUNTIFS($B$1:$B1,"<="&$B2,$D$1:$D1,">="&$B2)+1


WBD
 
Upvote 0
tagteam3283 said:
OMG that was quick.

I've pasted that into the workbook and it works a treat. Any chance you could explain how that works please?
Click to expand...

About formula in post 2
=COUNTIFS(A$2:A2,A2,D$2:D2,">="&B2)

Observe that the formula uses progressive ranges, that is the ranges increase when the formula is copied down.
In E2
A$2:A2 and D$2:D2
in E3
A$2:A3 and D$2:D3
in E4
A$2:A4 and D$2:D4
and so on

On each row the value in column B (Start Time) is compared with the values of column D of the current and precedent rows. If the value in D is greater or equal value in column B of current row it means it's a concurrent call. That's it!

M.
 
Last edited:
Upvote 0
Similar solution for mine. I had already worked on the solution when I saw that Marcelo had already posted a reply. Either will work I'm sure.

WBD
 
Upvote 0
Thanks both.

If the data was changed like this below to include the date and handsetID, how can i factor in the handset ID as well please?

I've already amended the formula to include the date so it allows me to run across a number of days, now I need to isolate handsets where all the data is on a single sheets:

Formula column D: =COUNTIFS($B$1:$B1,"<="&$B2,$C$1:$C1,">="&$B2)+1

[TABLE="width: 489"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]HandsetID[/TD]
[TD]Call Start[/TD]
[TD]Call End[/TD]
[TD]Calculation[/TD]
[/TR]
[TR]
[TD]tele#1[/TD]
[TD="align: right"]01/05/2018 00:55[/TD]
[TD="align: right"]01/05/2018 01:56[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]tele#1[/TD]
[TD="align: right"]01/05/2018 02:57[/TD]
[TD="align: right"]01/05/2018 03:57[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]tele#1[/TD]
[TD="align: right"]07/05/2018 21:29[/TD]
[TD="align: right"]07/05/2018 22:29[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]tele#1[/TD]
[TD="align: right"]07/05/2018 22:31[/TD]
[TD="align: right"]07/05/2018 23:32[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]tele#1[/TD]
[TD="align: right"]07/05/2018 23:33[/TD]
[TD="align: right"]08/05/2018 00:34[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]tele#1[/TD]
[TD="align: right"]08/05/2018 00:34[/TD]
[TD="align: right"]08/05/2018 01:34[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]tele#1[/TD]
[TD="align: right"]08/05/2018 01:32[/TD]
[TD="align: right"]08/05/2018 02:28[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]tele#1[/TD]
[TD="align: right"]08/05/2018 01:34[/TD]
[TD="align: right"]08/05/2018 01:34[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]tele#1[/TD]
[TD="align: right"]08/05/2018 01:42[/TD]
[TD="align: right"]08/05/2018 01:49[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]tele#1[/TD]
[TD="align: right"]08/05/2018 02:32[/TD]
[TD="align: right"]08/05/2018 03:12[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]tele#1[/TD]
[TD="align: right"]08/05/2018 03:23[/TD]
[TD="align: right"]08/05/2018 04:14[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]tele#1[/TD]
[TD="align: right"]08/05/2018 03:36[/TD]
[TD="align: right"]08/05/2018 03:49[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]tele#1[/TD]
[TD="align: right"]08/05/2018 04:12[/TD]
[TD="align: right"]08/05/2018 04:17[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]tele#1[/TD]
[TD="align: right"]08/05/2018 09:08[/TD]
[TD="align: right"]08/05/2018 10:09[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]tele#2[/TD]
[TD="align: right"]01/05/2018 01:56[/TD]
[TD="align: right"]01/05/2018 02:56[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]tele#2[/TD]
[TD="align: right"]01/05/2018 02:57[/TD]
[TD="align: right"]01/05/2018 03:57[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]tele#2[/TD]
[TD="align: right"]01/05/2018 03:51[/TD]
[TD="align: right"]01/05/2018 04:43[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]tele#2[/TD]
[TD="align: right"]01/05/2018 04:04[/TD]
[TD="align: right"]01/05/2018 04:17[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]tele#2[/TD]
[TD="align: right"]01/05/2018 04:51[/TD]
[TD="align: right"]01/05/2018 05:21[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]tele#2[/TD]
[TD="align: right"]01/05/2018 09:16[/TD]
[TD="align: right"]01/05/2018 10:16[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]tele#2[/TD]
[TD="align: right"]01/05/2018 09:30[/TD]
[TD="align: right"]01/05/2018 09:30[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]tele#2[/TD]
[TD="align: right"]01/05/2018 10:18[/TD]
[TD="align: right"]01/05/2018 10:18[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]tele#2[/TD]
[TD="align: right"]01/05/2018 10:26[/TD]
[TD="align: right"]01/05/2018 10:26[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]tele#2[/TD]
[TD="align: right"]01/05/2018 11:03[/TD]
[TD="align: right"]01/05/2018 11:04[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]tele#2[/TD]
[TD="align: right"]01/05/2018 13:13[/TD]
[TD="align: right"]01/05/2018 14:12[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]tele#2[/TD]
[TD="align: right"]01/05/2018 14:00[/TD]
[TD="align: right"]01/05/2018 14:47[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]tele#2[/TD]
[TD="align: right"]01/05/2018 14:57[/TD]
[TD="align: right"]01/05/2018 15:53[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]tele#2[/TD]
[TD="align: right"]01/05/2018 15:18[/TD]
[TD="align: right"]01/05/2018 15:38[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]tele#2[/TD]
[TD="align: right"]01/05/2018 16:05[/TD]
[TD="align: right"]01/05/2018 16:47[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]tele#2[/TD]
[TD="align: right"]01/05/2018 15:51[/TD]
[TD="align: right"]01/05/2018 15:51[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Excel Pivot Table to see the number of "Lost Calls"
Replies
0
Views
54
Bernie Galve
B
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
235
dshtesy
D
A
  • Solved
Count entry by time
Replies
3
Views
155
admvlad
A
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
228
Raj1979
R
M
  • Question Question
Pivot table not showing as Numbers
Replies
1
Views
266
shift-del
shift-del

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top