Calculating Net working time from concurrent transactions

Aladdin10

New Member
Joined
Jul 19, 2021
Messages
5
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
Can you post a sample of your data? Is there an identifying column that would differentiate one transaction from another?
 

Aladdin10

New Member
Joined
Jul 19, 2021
Messages
5
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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.

1626809103404.png



your help is much appreciated.
 

Crystalyzer

Well-known Member
Joined
Oct 18, 2011
Messages
688
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Jul 19, 2021
Messages
5
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Book1.xlsx
ABCDEFG
1Transaction IDStartTimeFinishTimeTransaction LengthAgent nameConcurrency at startConcurrency at end
2gia3a57vrt16/07/2021 9:2316/07/2021 9:460:23:03YLMAmirKa00
32u2c3s1laj16/07/2021 10:1116/07/2021 10:250:13:49YLMAmirKa00
4ukq1nx1sb916/07/2021 10:4616/07/2021 11:000:13:55YLMAmirKa01
5akdw80yyjj16/07/2021 10:5916/07/2021 11:100:11:01YLMAmirKa11
6cut8hw3jnd16/07/2021 11:0116/07/2021 11:440:43:12YLMAmirKa10
7ju0ry6h3wx16/07/2021 11:0616/07/2021 11:070:01:02YLMAmirKa22
8eyloanb1gn16/07/2021 11:2716/07/2021 11:380:11:18YLMAmirKa11
9o94ghmq8b916/07/2021 11:5416/07/2021 11:580:04:03YLMAmirKa00
10sydltxuyoa16/07/2021 11:5916/07/2021 12:010:01:40YLMAmirKa00
11eimerloobt16/07/2021 12:2316/07/2021 12:480:24:47YLMAmirKa01
12nxuh8lyluf16/07/2021 12:4616/07/2021 13:040:17:39YLMAmirKa10
13a2wk4ijdqx16/07/2021 16:1716/07/2021 16:200:03:46YLMAmirKa01
14hs9cioudpa16/07/2021 16:1916/07/2021 16:260:07:39YLMAmirKa10
153orp8ht6fa16/07/2021 16:4216/07/2021 16:560:14:06YLMAmirKa01
16jxkjmn3tjr16/07/2021 16:4916/07/2021 17:230:34:22YLMAmirKa11
17kmbdb3anpf16/07/2021 17:2016/07/2021 17:290:09:34YLMAmirKa11
1873wuamjd8n16/07/2021 17:2416/07/2021 17:330:09:03YLMAmirKa10
Sheet1
 

Aladdin10

New Member
Joined
Jul 19, 2021
Messages
5
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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
ABCDEFGHIK
1Transaction IDStartTimehelper1Transaction LengthAgent nameConcurrency at startConcurrency at endnon overlapping transaction length
2gia3a57vrt16/07/2021 0:009:23:509:46:53########0:23:03YLMAmirKa000:23:03
32u2c3s1laj16/07/2021 0:0010:11:3510:25:25########0:13:49YLMAmirKa000:13:49
4ukq1nx1sb916/07/2021 0:0010:46:1711:00:120:00:300:13:55YLMAmirKa010:13:25
5akdw80yyjj16/07/2021 0:0010:59:4211:10:430:09:090:11:01YLMAmirKa110:09:09
6cut8hw3jnd16/07/2021 0:0011:01:3411:44:460:38:260:43:12YLMAmirKa100:34:03
7ju0ry6h3wx16/07/2021 0:0011:06:2011:07:23########0:01:02YLMAmirKa220
8eyloanb1gn16/07/2021 0:0011:27:1911:38:37########0:11:18YLMAmirKa110
9o94ghmq8b916/07/2021 0:0011:54:5411:58:57########0:04:03YLMAmirKa000:04:03
10sydltxuyoa16/07/2021 0:0011:59:4212:01:23########0:01:40YLMAmirKa000:01:40
11eimerloobt16/07/2021 0:0012:23:3012:48:170:01:300:24:47YLMAmirKa010:23:17
12nxuh8lyluf16/07/2021 0:0012:46:471:04:27########0:17:39YLMAmirKa100:17:39
13a2wk4ijdqx16/07/2021 0:004:17:094:20:560:01:560:03:46YLMAmirKa010:01:50
14hs9cioudpa16/07/2021 0:004:19:004:26:39########0:07:39YLMAmirKa100:07:39
153orp8ht6fa16/07/2021 0:004:42:254:56:320:07:320:14:06YLMAmirKa010:06:34
16jxkjmn3tjr16/07/2021 0:004:49:005:23:230:03:160:34:22YLMAmirKa110:31:06
17kmbdb3anpf16/07/2021 0:005:20:075:29:410:05:240:09:34YLMAmirKa110:04:10
1873wuamjd8n16/07/2021 0:005:24:175:33:205:33:200:09:03YLMAmirKa100:09:03
192:03:34
Sheet1
Cell Formulas
RangeFormula
K4,K15:K17,K13,K11K4=F4-E4
K5K5=E5
K6K6=D6-D5
E2:E18E2=D2-C3
K18K18=F18
K19K19=SUM(K2:K18)
 

Aladdin10

New Member
Joined
Jul 19, 2021
Messages
5
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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.
 

Forum statistics

Threads
1,141,068
Messages
5,704,088
Members
421,327
Latest member
Msh

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
Top