How to find productive time from a chat report

U

uakash7

New Member
Joined
Apr 1, 2019
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.[TABLE="width: 500"]
<tbody>[TR]
[TD]Chat Start Time[/TD]
[TD]Chat End Time[/TD]
[TD]Resource ID[/TD]
[TD]Chat Time[/TD]
[TD]Break Time[/TD]
[/TR]
[TR]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]12/11/2018 23:09[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]12/11/2018 23:49[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77"]RC1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]0:40:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]0:40:00[/TD]
[/TR]
[TR]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]12/12/2018 0:10[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]12/12/2018 0:28[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77"]RC1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]0:17:52[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95, align: right"]0:21:16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]12/12/2018 0:20[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]12/12/2018 0:48[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77"]RC1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]0:27:52[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95, align: right"]0:00:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]12/12/2018 0:50[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]12/12/2018 1:28[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77"]RC1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]0:37:52[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95, align: right"]0:02:08[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]12/12/2018 2:46[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]12/12/2018 4:54[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]RC1[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]2:08:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95, align: right"]1:17:52[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]12/12/2018 5:09[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]12/12/2018 6:27[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77"]RC1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]1:18:26[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95, align: right"]0:15:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]12/12/2018 5:10[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111, align: right"]12/12/2018 5:28[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77"]RC1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]0:17:52[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total chat time[/TD]
[TD][/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]5:47:54[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95, align: right"]1:56:16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total break time[/TD]
[TD][/TD]
[TD][TABLE="width: 95"]
<tbody>[TR]
[TD="class: xl65, width: 95, align: right"]1:56:16[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][TABLE="width: 111"]
<tbody>[TR]
[TD="class: xl65, width: 111"]Total Login time[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]7:18:26[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]This is required[/TD]
[TD]Total Productive time[/TD]
[TD]
[/TD]
[TD][TABLE="width: 77"]
<tbody>[TR]
[TD="class: xl65, width: 77, align: right"]5:22:10[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
isnt that just a calculation
total login time - total break time ?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Count entry by time
Replies
3
Views
253
admvlad
A
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
423
dshtesy
D
D
  • Question Question
Locate first instance of all dates in a column and hide all remaining rows
Replies
4
Views
126
Peter_SSs
Peter_SSs
V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
506
etaf
etaf
S
  • Question Question
Formatting Midnight in a date to 24 instead of 0:00
Replies
4
Views
613
shepcs
S

Forum statistics

Threads
1,224,824
Messages
6,181,187
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

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