• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pure Engaged Time

U

uakash7

New Member
Hi, I get a report of chats handled by resources. The resources login in late PM hours and logout in next day morning however, there are some agents who login after midnight and logout in morning. They do take breaks during their shift and they can work on multiple chats at a time. I need to figure out what is the pure occupied time agents are spending on chats, removing all the breaks and any idle time. I've attached an excel sheet with sample data and desired output. Please let me know if you have any doubts. In the example below - the first chat of 11th Dec starts at 23:09 and ends at 23:49 and last chat of this day ends at 6:27 AM. Total chat length of the day is 5:47:54, total login time is 7:18:26, total break time is 1:56:16 and total engage time of the resource is (login time -break time) 5:22:10. If you can help with some formula or macro to make this calculation easy, that would be great.
Start TimeEnd TimeResourceID
12/11/2018 6:10​
12/11/2018 6:28​
RC1
12/11/2018 6:19​
12/11/2018 6:42​
RC1
12/11/2018 6:46​
12/11/2018 6:56​
RC1
12/11/2018 23:09​
12/11/2018 23:49​
RC1
12/12/2018 0:10​
12/12/2018 0:28​
RC1
12/12/2018 0:20​
12/12/2018 0:48​
RC1
12/12/2018 0:50​
12/12/2018 1:28​
RC1
12/12/2018 2:46​
12/12/2018 4:54​
RC1
12/12/2018 5:09​
12/12/2018 6:27​
RC1
12/12/2018 5:10​
12/12/2018 5:28​
RC1
12/12/2018 23:01​
12/12/2018 23:51​
RC1
12/12/2018 23:56​
12/13/2018 1:38​
RC1
 

Attachments

  • SampleChatData.xlsx
    17.2 KB · Views: 9
ANKUSHRS1 said:
Here it is VBA based solution.
Click to expand...
Hi ANKUSHRS1,

It fails on the broader data, I apply the same on the sample data in the "Sample Data" sheet and it doesn't give desired result. Please see the sample data, total engage time is 17:47:41 however in your solution it shows as 20:55:21.
 
uakash7 said:
Hi ANKUSHRS1,

It fails on the broader data, I apply the same on the sample data in the "Sample Data" sheet and it doesn't give desired result. Please see the sample data, total engage time is 17:47:41 however in your solution it shows as 20:55:21.
Click to expand...
Your duty starting time and ending time is not fixed...Running all data where many dates include ends up wrong output.
With little modification in this macro you can get desired output by running only single shift data.
 
vletm said:
uakash7
Are You looking for DAILY or SHIFT hours?
How many hours gap is between 'previous hours'?
Click to expand...
Hi, I'm looking for shift hours, there is a gap of at lease 10 hours. First shift login time is 9 PM and last shift logout is 11 AM. From 11 AM to 9 PM it's closed.
 
uakash7
Okay -- shifts times.
Next. eg RC60 have overlaps!
How do those would handle? No matter how many overlaps or as one chat per one time?
Below as used times -- maybe same as Your used term the pure occupied time.
Screenshot 2019-03-31 at 19.42.54.png
I compared with Your results ... how did You take care shifts?
 
vletm said:
uakash7
Okay -- shifts times.
Next. eg RC60 have overlaps!
How do those would handle? No matter how many overlaps or as one chat per one time?
Below as used times -- maybe same as Your used term the pure occupied time.
View attachment 59145
I compared with Your results ... how did You take care shifts?
Click to expand...
No matter how many overlaps. For example - RC60 takes first chat of the shift on 2-Dec-2018 at 10:08:21 PM and last chat of the shift finishes at 3-Dec-2018 6:23:02 AM. In this shift total login time was 8:14:41 (time difference between first chat start time and last chat end time) and total chat time is 16:52:02 (sum of all chat lengths). I need the total productive time in the shift which is login time less breaks if there is any (in this case 8:14:41 hours since RC60 has not taken any break in this shift). He was busy on one or more chats during whole shift and there was no idle time or break.
Another example - For RC5 first chat starts on 24-Feb-2019 at 10:09:19 PM and last chat ends on 25-Feb-2019 at 5:52:22 AM. Total login time is 7:43:03 hours, however RC5 was on idle or on break frequently during the shift (total of 4:24:02 hours), hence total productive time in the shift was only 3:19:01 hours (7:43:03 - 4:24:02). These breaks or idle time in the shift make my job difficult.
I am looking for the productive time (or pure occupied time) which is 7:43:03 and 3:19:01 respectively in the examples above.
 
ANKUSHRS1 said:
Your duty starting time and ending time is not fixed...Running all data where many dates include ends up wrong output.
With little modification in this macro you can get desired output by running only single shift data.
Click to expand...
@ANKUSHRS1 - I tried with one resource for one day shift and it did not work. For example - resource RC60 in the sample data has engage time of 8:42:49 and no break time, however in your solution it shows 6:49:42 engaged hours with 1:53:07 hours of break.
 
vletm said:
uakash7
I didn't get all needed answers.
Press [ Do It ]-button
Click to expand...
@vletm - thank you for your help, we are pretty close. I tested with the sample data and here are the findings. The shift end time was not correctly updated for RC60 and RC5, reason being the last chat end time was not picked correctly, here last chat end time is 6:51, however VBA picked 6:23 AM. There are two such instances.
12/3/2018 5:49​
12/3/2018 6:51​
RC60
12/3/2018 5:57​
12/3/2018 6:23​
RC60

Second, the break was not updated correctly. RC60 did not take any break and he was busy on one or more chat during the whole shift, however VBA picked break as - 1:08:21. Break time was only correct for RC1 on 11/12/18. It could because chats are not ending in the same order it's starting. For example - First chat ends in last.
2/25/2019 0:20​
2/25/2019 0:58​
RC5
2/25/2019 0:31​
2/25/2019 0:44​
RC5
2/25/2019 0:46​
2/25/2019 0:49​
RC5

I'm sorry I didn't share all the needed answers. Do let me know if you've any more question.
Regarding shifts, they are managed separately, there are some resources who handle chats only for few hours. At times, resources sit idle for long time as well due to low chat volume.
 
uakash7
You're using some 'terms' ... okay, I don't need those.
a) The shift end time: one typo, modified
b) BreakTime with RC60: Do we use same data? For me, below shows breaks for RC60! I added here colors!
Screenshot 2019-04-01 at 19.44.42.png
Normally, yellow/'no color' shows those breaks.
c) I already made my questions and I didn't get answers.
 

Attachments

  • SampleChatData.xlsb
    32.2 KB · Views: 2
vletm said:
uakash7
You're using some 'terms' ... okay, I don't need those.
a) The shift end time: one typo, modified
b) BreakTime with RC60: Do we use same data? For me, below shows breaks for RC60! I added here colors!
View attachment 59167
Normally, yellow/'no color' shows those breaks.
c) I already made my questions and I didn't get answers.
Click to expand...
@vletm - You're a genius. We are just left with correcting break time calculation. Yes, I see breaks of RC60 now. However, there are only two breaks from 4:13 to 5:19 and from 5:47 to 5:49, total of 1:07:21 hours wherein VBA is showing as 1:53:07.
Let me try to answer your questions again = Shift timing - 9 hours, starting from 9 PM to 11 AM, resources login as per their schedule. Thanks again for all your efforts.
 
@vletm - Thanks a ton ! This serves my purpose. I tested this on some more data and looked perfect. Can I ask to add the max number of chats handled at a time if this is not too much? You can add a similar table at the end of the previous outputs. I'm sure you can do it in a jiffy.
 
vletm said:
You can ask ...
but then You gotta explain: what?
And with clear visual example - less words!
Click to expand...
Great, I want to see max concurrent chats handled by a resource on each day. I've taken example of RC1 for one shift as below -

59195
 
Try to 'google' LESS and
how to notice, which sentence has a question?
 

Attachments

  • SampleChatData.xlsb
    37.1 KB · Views: 13
You must log in or register to reply here.
Back
Top