Concurrent situations

[Gcook90]

Hi all,

Working on a spreadsheet which shows telephone calls:

F column is Start time of the call (e.g. 21/05/2024 12:30)
J column is End time of the call (e.g. 21/05/2024 12:35)
K column is duration of the call in minutes and seconds (e.g. 5m 20s)

Is there a way to work out how many calls took place concurrently at the same time as each call based on this data?

Thanks in advance!

 

[Gcook90]

Can you post your expected results.

Started working through it - it's quite tricky to count how many maximum concurrent calls there are for the longer calls - but i have done it below as an example:

Start	End	Duration	Expected	Fluff formula
24/04/2024 00:14:07​	24/04/2024 00:14:38​	30s	1	1
24/04/2024 02:10:34​	24/04/2024 02:10:57​	22s	1	1
24/04/2024 03:19:18​	24/04/2024 03:19:33​	13s	1	1
24/04/2024 04:18:35​	24/04/2024 04:18:45​	8s	1	1
24/04/2024 06:23:53​	24/04/2024 06:23:59​	5s	1	1
24/04/2024 06:47:21​	24/04/2024 06:47:32​	10s	1	1
24/04/2024 06:50:24​	24/04/2024 06:50:44​	19s	1	1
24/04/2024 07:01:10​	24/04/2024 07:01:21​	10s	1	1
24/04/2024 07:04:58​	24/04/2024 07:05:18​	19s	1	1
24/04/2024 07:22:29​	24/04/2024 07:22:47​	17s	1	1
24/04/2024 07:38:48​	24/04/2024 07:39:22​	33s	1	1
24/04/2024 07:52:35​	24/04/2024 07:52:41​	4s	1	1
24/04/2024 07:57:53​	24/04/2024 07:58:13​	19s	1	1
24/04/2024 07:58:18​	24/04/2024 07:58:33​	14s	1	1
24/04/2024 08:12:09​	24/04/2024 08:12:14​	4s	1	1
24/04/2024 08:16:27​	24/04/2024 08:17:20​	52s	1	1
24/04/2024 08:17:36​	24/04/2024 08:29:02​	11m 25s	2	3
24/04/2024 08:25:52​	24/04/2024 08:26:08​	15s	2	2
24/04/2024 08:27:11​	24/04/2024 08:27:22​	10s	2	2
24/04/2024 08:29:35​	24/04/2024 08:29:54​	18s	1	1
24/04/2024 08:30:52​	24/04/2024 08:30:54​		1	1
24/04/2024 08:31:06​	24/04/2024 08:37:47​	6m 9s	2	3
24/04/2024 08:33:33​	24/04/2024 08:33:53​	19s	2	2
24/04/2024 08:36:54​	24/04/2024 08:51:55​	15m	3	16
24/04/2024 08:37:55​	24/04/2024 08:39:00​	1m 4s		2
24/04/2024 08:40:07​	24/04/2024 08:58:59​	18m 51s		22
24/04/2024 08:40:22​	24/04/2024 08:40:30​	7s		3
24/04/2024 08:41:43​	24/04/2024 08:42:48​	1m 4s		3
24/04/2024 08:43:49​	24/04/2024 08:44:09​			4
24/04/2024 08:44:05​	24/04/2024 08:44:09​	3s		4
24/04/2024 08:44:16​	24/04/2024 08:44:48​	31s		3
24/04/2024 08:45:08​	24/04/2024 08:45:15​	6s		3
24/04/2024 08:47:09​	24/04/2024 08:47:29​	19s		4
24/04/2024 08:47:19​	24/04/2024 08:47:24​	4s		4
24/04/2024 08:47:37​	24/04/2024 09:05:12​	17m 35s		28
24/04/2024 08:50:09​	24/04/2024 08:50:30​	21s		4
24/04/2024 08:50:33​	24/04/2024 08:54:03​	3m 30s		5
24/04/2024 08:51:16​	24/04/2024 08:51:35​	18s		5

 

[Fluff]

Thanks for that
Can you explain how you get those results, as I don't see how you get the values highlighted in blue

Fluff.xlsm
	A	B	C	D
18	24/04/2024 08:17:36	24/04/2024 08:29:02	11m 25s	2
19	24/04/2024 08:25:52	24/04/2024 08:26:08	15s	2
20	24/04/2024 08:27:11	24/04/2024 08:27:22	10s	2
21	24/04/2024 08:29:35	24/04/2024 08:29:54	18s	1
22	24/04/2024 08:30:52	24/04/2024 08:30:54		1
23	24/04/2024 08:31:06	24/04/2024 08:37:47	6m 9s	2
24	24/04/2024 08:33:33	24/04/2024 08:33:53	19s	2
25	24/04/2024 08:36:54	24/04/2024 08:51:55	15m	3
Sheet6

 

[Gcook90]

Row 18 - expected 2 - because the call took place between 08:17:36 and 08:29:02 and there was only a maximum of one other call which was taking place at the same time as this (there was never a time when this call happened simultaneously with 2 other calls). Same for row 23.

Row 25 I manually worked out there was a situation where there were 2 other calls going on at some point. (so 3 calls total) - it has counted 16 which is just all the calls which are happening during that 15minute window, but not the maximum amount concurrently.

 

[Vogateer]

I want to make sure I'm understanding correctly, so would it be accurate to say you're looking for the maximum amount of active phone lines during each call?

For example, one phone line might have a call that lasts 10 minutes, and a second phone line might sequentially have 5 calls during that same time, but there's never more than 2 active calls going on at the same time. So you would want the cell to say 2, right?

 

[Gcook90]

I want to make sure I'm understanding correctly, so would it be accurate to say you're looking for the maximum amount of active phone lines during each call?

For example, one phone line might have a call that lasts 10 minutes, and a second phone line might sequentially have 5 calls during that same time, but there's never more than 2 active calls going on at the same time. So you would want the cell to say 2, right?

Correct

 

[Fluff]

Surely rows 19 & 20 both took place at the same time as row 18, so it should be 3 not 2?

 

[Gcook90]

Surely rows 19 & 20 both took place at the same time as row 18, so it should be 3 not 2?

Row 18 only happened at the same time as any one of these calls concurrenly.
As 18 occured, 19 started, 19 ended, 20 started, 20 ended, 18 ended.

24/04/2024 08:17:36​	24/04/2024 08:29:02​	11m 25s	2	3
24/04/2024 08:25:52​	24/04/2024 08:26:08​	15s	2	2
24/04/2024 08:27:11​	24/04/2024 08:27:22​	10s	2	2

 

[Fluff]

In that case I don't understand why rows 19 & 20 should return 2 as there is only one other call running at the same time.

 

[Gcook90]

I've just including that call itself too.
If you have a formula that you think would work how you've just said it, I can work with that.

 

[Fluff]

Unfortunately I don't, as I don't really understand what your criteria are.