Calculating Net working time from concurrent transactions

[Aladdin10]

Hi,

I'm trying to calculate the net working minutes from a raw data that provids transactions with start and end time in the following format: 16/07/2021 9:23:00 AM and that would be all in one cell only.
The issue is that, there are concurrent transactions, which means (for example) we will have one that started 9:23:00 AM and ended at 9:45:00 AM, and another that started 9:32:00AM and endedat 9:58:00 AM.
If we consider every transaction alone to calculate the time we will get 22 mins for the first transaction and 26 mins for the second. But adding them is not right since they are overlapping, so the actual time spent should be 35 mins as net time spent in transactions.
Now I get hundreds of overlapping transactions and I can't seem to find a way to calculate the net time spent working on transactions per day.

Any help would be appreciated.
Thanks
Aladdin

 

[Crystalyzer]

Can you post a sample of your data? Is there an identifying column that would differentiate one transaction from another?

 

[Aladdin10]

Hi Crystalizer,

Thank you for taking the time to review this one.
This is a sample from the raw data, so these are the transactions handled by agent's login ID which is unique for every agent.

The purpose is to calculate the total working minutes for every agent on every day.

your help is much appreciated.

 

[Crystalyzer]

If you could post the copy of the worksheet with the XL2BB tool it would be MUCH easier for me to recreate the data to try to solve. There are threads on this tool in the FAQ's.

 

[Aladdin10]

Book1.xlsx
	A	B	C	D	E	F	G
1	Transaction ID	StartTime	FinishTime	Transaction Length	Agent name	Concurrency at start	Concurrency at end
2	gia3a57vrt	16/07/2021 9:23	16/07/2021 9:46	0:23:03	YLMAmirKa	0	0
3	2u2c3s1laj	16/07/2021 10:11	16/07/2021 10:25	0:13:49	YLMAmirKa	0	0
4	ukq1nx1sb9	16/07/2021 10:46	16/07/2021 11:00	0:13:55	YLMAmirKa	0	1
5	akdw80yyjj	16/07/2021 10:59	16/07/2021 11:10	0:11:01	YLMAmirKa	1	1
6	cut8hw3jnd	16/07/2021 11:01	16/07/2021 11:44	0:43:12	YLMAmirKa	1	0
7	ju0ry6h3wx	16/07/2021 11:06	16/07/2021 11:07	0:01:02	YLMAmirKa	2	2
8	eyloanb1gn	16/07/2021 11:27	16/07/2021 11:38	0:11:18	YLMAmirKa	1	1
9	o94ghmq8b9	16/07/2021 11:54	16/07/2021 11:58	0:04:03	YLMAmirKa	0	0
10	sydltxuyoa	16/07/2021 11:59	16/07/2021 12:01	0:01:40	YLMAmirKa	0	0
11	eimerloobt	16/07/2021 12:23	16/07/2021 12:48	0:24:47	YLMAmirKa	0	1
12	nxuh8lyluf	16/07/2021 12:46	16/07/2021 13:04	0:17:39	YLMAmirKa	1	0
13	a2wk4ijdqx	16/07/2021 16:17	16/07/2021 16:20	0:03:46	YLMAmirKa	0	1
14	hs9cioudpa	16/07/2021 16:19	16/07/2021 16:26	0:07:39	YLMAmirKa	1	0
15	3orp8ht6fa	16/07/2021 16:42	16/07/2021 16:56	0:14:06	YLMAmirKa	0	1
16	jxkjmn3tjr	16/07/2021 16:49	16/07/2021 17:23	0:34:22	YLMAmirKa	1	1
17	kmbdb3anpf	16/07/2021 17:20	16/07/2021 17:29	0:09:34	YLMAmirKa	1	1
18	73wuamjd8n	16/07/2021 17:24	16/07/2021 17:33	0:09:03	YLMAmirKa	1	0
Sheet1

 

[Aladdin10]

I've also calculated the outcome in this case manually as shown below and it should be 2 hours 3 minutes and 34 seconds:

Book1.xlsx
	A	B	C	D	E	F	G	H	I		K
1	Transaction ID	StartTime			helper1	Transaction Length	Agent name	Concurrency at start	Concurrency at end		non overlapping transaction length
2	gia3a57vrt	16/07/2021 0:00	9:23:50	9:46:53	########	0:23:03	YLMAmirKa	0	0		0:23:03
3	2u2c3s1laj	16/07/2021 0:00	10:11:35	10:25:25	########	0:13:49	YLMAmirKa	0	0		0:13:49
4	ukq1nx1sb9	16/07/2021 0:00	10:46:17	11:00:12	0:00:30	0:13:55	YLMAmirKa	0	1		0:13:25
5	akdw80yyjj	16/07/2021 0:00	10:59:42	11:10:43	0:09:09	0:11:01	YLMAmirKa	1	1		0:09:09
6	cut8hw3jnd	16/07/2021 0:00	11:01:34	11:44:46	0:38:26	0:43:12	YLMAmirKa	1	0		0:34:03
7	ju0ry6h3wx	16/07/2021 0:00	11:06:20	11:07:23	########	0:01:02	YLMAmirKa	2	2		0
8	eyloanb1gn	16/07/2021 0:00	11:27:19	11:38:37	########	0:11:18	YLMAmirKa	1	1		0
9	o94ghmq8b9	16/07/2021 0:00	11:54:54	11:58:57	########	0:04:03	YLMAmirKa	0	0		0:04:03
10	sydltxuyoa	16/07/2021 0:00	11:59:42	12:01:23	########	0:01:40	YLMAmirKa	0	0		0:01:40
11	eimerloobt	16/07/2021 0:00	12:23:30	12:48:17	0:01:30	0:24:47	YLMAmirKa	0	1		0:23:17
12	nxuh8lyluf	16/07/2021 0:00	12:46:47	1:04:27	########	0:17:39	YLMAmirKa	1	0		0:17:39
13	a2wk4ijdqx	16/07/2021 0:00	4:17:09	4:20:56	0:01:56	0:03:46	YLMAmirKa	0	1		0:01:50
14	hs9cioudpa	16/07/2021 0:00	4:19:00	4:26:39	########	0:07:39	YLMAmirKa	1	0		0:07:39
15	3orp8ht6fa	16/07/2021 0:00	4:42:25	4:56:32	0:07:32	0:14:06	YLMAmirKa	0	1		0:06:34
16	jxkjmn3tjr	16/07/2021 0:00	4:49:00	5:23:23	0:03:16	0:34:22	YLMAmirKa	1	1		0:31:06
17	kmbdb3anpf	16/07/2021 0:00	5:20:07	5:29:41	0:05:24	0:09:34	YLMAmirKa	1	1		0:04:10
18	73wuamjd8n	16/07/2021 0:00	5:24:17	5:33:20	5:33:20	0:09:03	YLMAmirKa	1	0		0:09:03
19											2:03:34
Sheet1

Cell Formulas
Range	Formula
K4,K15:K17,K13,K11	K4	=F4-E4
K5	K5	=E5
K6	K6	=D6-D5
E2:E18	E2	=D2-C3
K18	K18	=F18
K19	K19	=SUM(K2:K18)

 

[Aladdin10]

The idea is that I get alot of overlapping transactions, at some point I can't tell in an equation which to include, which to include paritally and which to exclude totally because the time spent in this transaction is already considered in other transaction/s.