How to find productive time from a chat report

[uakash7]

Hi, I am looking for convenient way to find productive time of resources working in shifts to handle chats. I get chat handled report with start time, end time and resource ID who handled it. They can work of one or more chats at at time and there can be multiple breaks or idle time during the shift. First shift logs in at 9 PM and last shift logs out at 11 AM. Please help me to figure this out, let me know if you need any further details. I have shared a sample data and example with calculation.

Chat Start Time	Chat End Time	Resource ID	Chat Time	Break Time
12/11/2018 23:09 <tbody> </tbody>	12/11/2018 23:49 <tbody> </tbody>	RC1 <tbody> </tbody>	0:40:00 <tbody> </tbody>	0:40:00
12/12/2018 0:10 <tbody> </tbody>	12/12/2018 0:28 <tbody> </tbody>	RC1 <tbody> </tbody>	0:17:52 <tbody> </tbody>	0:21:16 <tbody> </tbody>
12/12/2018 0:20 <tbody> </tbody>	12/12/2018 0:48 <tbody> </tbody>	RC1 <tbody> </tbody>	0:27:52 <tbody> </tbody>	0:00:00 <tbody> </tbody>
12/12/2018 0:50 <tbody> </tbody>	12/12/2018 1:28 <tbody> </tbody>	RC1 <tbody> </tbody>	0:37:52 <tbody> </tbody>	0:02:08 <tbody> </tbody>
12/12/2018 2:46 <tbody> </tbody>	12/12/2018 4:54 <tbody> </tbody>	RC1	2:08:00 <tbody> </tbody>	1:17:52 <tbody> </tbody>
12/12/2018 5:09 <tbody> </tbody>	12/12/2018 6:27 <tbody> </tbody>	RC1 <tbody> </tbody>	1:18:26 <tbody> </tbody>	0:15:00 <tbody> </tbody>
12/12/2018 5:10 <tbody> </tbody>	12/12/2018 5:28 <tbody> </tbody>	RC1 <tbody> </tbody>	0:17:52 <tbody> </tbody>
	Total chat time		5:47:54 <tbody> </tbody>	1:56:16 <tbody> </tbody>
	Total break time		1:56:16 <tbody> </tbody>
	Total Login time <tbody> </tbody>		7:18:26 <tbody> </tbody>
This is required	Total Productive time		5:22:10 <tbody> </tbody>

<tbody>
</tbody>

 

[etaf]

isnt that just a calculation
total login time - total break time ?

 

[uakash7]

Hi Wayne,
You're right, that's the calculation I'm looking for from excel formula or macro. I have done it manually for sample. Calculating break time from the raw data is a challenge since there are multiple chats going on and last chat in the list is not always last chat of the day.

 

[Kyle123]

xposted at EF

 

[Fluff]

Cross posted https://www.excelforum.com/excel-general/1270665-engaged-time-from-chat-report.html#post5093908

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[uakash7]

Any progress on this?