• 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.

Late coming identify

Status
Not open for further replies.

sms2luv

Member
I want to check late coming for employees.

I have their scheduled in this format.
0800 - 1700

When I remove login report I get data on this format.
07/23/2007 08:47:00

So if a emp logs in at 0847, its late by 47 minutes.

How can I calculate that using formula
 
sms2luv
without testing any data...
this would work ...
=if(mod(your_login_data,1) > time(8,0,0),"Late by " & text(mod(your_login_data,1) - time(8,0,0),"hh:mm"),"")
 
God bless everyone in the forum.
Thanks for response.
Attaching Sample file

I will update Roster and Login Sheet and want outcome to be Sheet1.
 

Attachments

  • testbook.xlsx
    10.6 KB · Views: 11
sms2luv ... You didn't test it.
but one question:
If no 'Login Time' ... is it late or not?
and
... I won't even start with only formulas?
 
If no login time its blank.
I wanted the data to look the way it is in Sheet1.
However didn't knew how to start.
Also if you have a better way to put it, please tell me
Sorry for trouble.
 
Hey Bro, you are God of Excel Resolution.
No words to express my happiness.
So kind of you.
If you don't mind can you explain the code.
Also in With Statement you have used
y= 2
Chk, Clng ....
I didn't understand

There would be 2 reason for no Login time.
Either he was Absent/Leave.
Agent came to work and didn't login.
 
sms2luv
If no 'Login Time' ... is it late or not?
... asking more without answers ...
With ... then no need to write again those after 'With'-texts
y= 2 ... 1st default row
Chk ... variables for values
Clng ... need to be then Matching dates
I understand that You don't ...yet.
ReRead code again row-by-row and think.
Next, You would run that code row-by-row.
 
Thanks
If no login, it should check whether agent had Off on that day, if its Off then it would be Woff.
If there are no week off, it should be Leave.
 
Forgot to tell you sorry.
If you can add 2 more column to Sheet1 stating
1.Target of login duration in Min for the day.
2.For how many minutes did agent logged in for.

Some agents are doing night shift, for example if agent is roster on 1 Aug 2017 23:00 to 08:00.
Then the target hours for 1st Aug should be 1 hour, and for 2nd Aug it should be 8 hours because the date changes after 00:00.
Staff time should also be calculated likewise

How I made the concept a bit clear, also let me know your suggestion.
 
sms2luv
If no 'Login Time' ... is it late or not?
Screen Shot 2017-07-27 at 23.10.05.png
Screen Shot 2017-07-27 at 23.10.18.png
Screen Shot 2017-07-27 at 23.11.28.png
If no login, it should check whether agent had Off on that day, if its Off then it would be Woff.
If there are no week off, it should be Leave.

Do we have same file or not?

and more more more coming...
Think ... think one more time ...
You should have the great idea before You would ask something.
Even small changes would change the whole code.

.. and use terms which could find from Your files!
 
Hey bro.
If no 'Login Time' ... is it late or not?
Its not late or early or on time
It can be either leave or off depending on roster
 
Do You company has policy like:
1) Do 'Rooster'
2) Work as You want
3) Change 'Rooster' as those are worked.
As in my example.
123's Rooster 04-Jul is 08:00-17:00 ... no Login Time.
And You could write:
Its not late or early or on time
It can be either leave or off depending on roster
Did I read those wrong way?
Here, that would be 'warning' and few times later ... no matter!
ps. It's no bro here!
 
Hi,
I am really sorry if I hurted you in any way.
You have helped me soon very Much, that it came out as bro.
Sorry again Sir for that.

One more thing Sir.
There are new changes in our Exony tool, the raw dump pattern as changed.
Attaching file for reference.
Based on Roster and Login sheet, I want to generate a new sheet main with the points mentioned.
Also our company policy is that you have to complete Staff time on daily basis.
Emp cannot work as per their preference.
 

Attachments

  • a1-f3-m-32ee33c911ba1da13fa47c04431ccff4ea74e116.xlsx
    78.2 KB · Views: 5
sms2luv
I try to help You, but ...
You won't answer even simply question! That's challenge!
As in my previous reply ...
hmm ... You wrote that not Your Company policy but as in that sample ... what?
Do You even try to read my replies at all?
As ps. It's no bro here!

> New File, New Rules ... but where? <
or do someone should guess those?
or dig those from someone who would give some answers?

Is it always TWO DAY-file?
Is it fixed layout?
Where is that original raw dump? Login!A:E ?
Need to know INPUT
and
wanted OUTPUT!

There are used terms which ... only You know!
Now, there are Login and Event stamps ...
... and still 'how to mark if 'break rules'?
 
Thanks for reply.
I will try to answer every thing possible, if you don't mind can I call you King, because I am very much impressed.

Company policy.
Employee is suppose to work for 5 days a week.(2 days week off)
Month starts from 1st.
Week starts from Sunday.
Week off and shift timing are not constant and can change every week.
There are 7 different shift timings.
Employees are rostered depending on call volumes.(Week off and Shift timings are decided by Supervisors only)
An employee might have to work in 2 different shifts, example 0800-1700 and other day 1100-2000 hours.
Emp has to come as per the roster only
Emp can plan leave in advance depending on availability.
Emp sometime do not report to work due to emergency, which is marked as leave or absent depending on severity of the reason.
We have 3 breaks, 15 min, 30 min 15 min. Total 1 hour.

.

Tools Usage and data
We use Cisco tool which records emp login/logout/breaks and other activities.
Tools creates events of all activities as shown in Dump(Login).
The tool has upgraded yesterday and now we get events for everything.
Once the roster is made, we paste it in Roster Sheet.


Data Points.
It can be 1,2 days or entire month data.
Raw dump saved in Login sheet.
When emp logs in a login event is created and when he logs out a logout event is created.
All events has time.
If emp logs in&out multiple times a day that event is also created.
Suppose if emp is doing his shift has 1 Aug 23:00 - 0800 and logs in and out on time as per the roster , a login event will be created as 1 Aug 23:00 and logout event would be created as 2 aug 08:00.
If emp is rostered on 2 Aug 23:00 - 0800, then he will login again at 2 Aug 23:00 and a new event would be created.
Agent is suppose to complete 540 min as per the schedule.
If he is doing 1 Aug 2300 - 0800 shift his login target would be 60 min as date will change post 00:00 hours.
If agent has week off on 2 Aug, his login target would be 480 min (previous shift).

I created extra column in Login sheet(gray), one for seconds to minutes conversion and 1 for emp id.
I created some pivot tables, so that I can use getpivotdata.
I wanted the Results(format) as shown in Main sheet, however if you think there is some better option, I would be glad.
Input is Login sheet and Roster sheet.
Output is Main sheet.
If emp wants to go on break, he selects break requires in Cisco,
In case emp is busy, he selects meeting or trainong.
This is done so that the call won't hit the agent when they put any Aux(breaks,meeting,training)

Hope I tried to explain in details..
Thanks once again for what you are doing for me.
 
This needs
1) ORIGINAL Raw Data-file from some days! Not even data sorted!
2) 'Roster'-file, which covers data-files dates!
( so far Your samples has been against any policy! )
3) File for output ... which has 'Main'-sheet.
of course, everything could be in same ... original raw file,
if Your 'system' can 'move somehow codes and so on' to it.

Q: What is 'login target'?
9hrs = 540mins
1hr breaks = 60min, breaks includes working
480mins ... 8hrs... hmm? no match!
Q: ... which is marked as leave or absent ... who marks?

I have my username ... I won't change it!
 
Vletm, nice username

This needs
1) ORIGINAL Raw Data-file from some days! Not even data sorted!
2) 'Roster'-file, which covers data-files dates!
( so far Your samples has been against any policy! )
3) File for output ... which has 'Main'-sheet.
of course, everything could be in same ... original raw file,


Attached is the raw dump of Cisco for approx 10 days with Rosters.
Both data are original just changed name.
File output can be a new sheet and wordbook.

Q: What is 'login target'?
9hrs = 540mins
1hr breaks = 60min, breaks includes working
480mins ... 8hrs... hmm? no match!
Login Target is 9 hours with breaks
.

1 hour = 60 min.
9 hours = 540 min.
Break is of 1 hour = 60 min.

To make it simple.
If Emp applies for leave before roster is made, its marked as Leave.
If Emp is absent without Supervisors being aware its marked as absent.
Both things are done by Supervisors.

I think you misunderstood my post.
If he is doing 1 Aug 2300 - 0800 shift his login target would be 60 min as date will change post 00:00 hours.
If agent has week off on 2 Aug, his login target would be 480 min (previous shift).
 

Attachments

  • Raw.xlsx
    221.2 KB · Views: 4
To make it simple.
I like that sentence!
...
If I have asked example: What is 'login target'?
The simple answer would start like: 'login target' is ....
... could You find that kind of answer somewhere?
or
from #18:
Emp sometime do not report to work due to emergency, which is marked as leave or absent depending on severity of the reason. ... and 'reply' ...hmm? ...
> Then using 'Roster'-file with 'Raw'-data, even 'Roster'-file have to be correct!
Not as so far with Your samples!

> 'the raw dump of Cisco'
- do it really sort 'someway' automatic?
- what would be that 'Duration'?
- as I have written (#16) There are used terms which ... only You know!
Screen Shot 2017-07-28 at 18.53.12.png
> (#16) or dig those from someone who would give some answers?
दुहराव
This seems to take a long time before I could start to do something!
 
1) Login Target is 540 minutes.
2) To make it more simple, let's mark leave for people not reporting to work, if they didn't come to work, when they are scheduled.
3) The latest file which I have sent is current Roster and Cisco Login, we are following, let's stick to same format.
4) Cisco is a tool which records all emp activities on the soft phone(short break, Lunch, Meeting...)
5) I thought I will not play around with the dump file(sorting), so that you can modify it the way you want.
6) Duration(duration of the event) is in Seconds and we have to convert it to Minutes.
7) Terms
No of login attempts : How many times did the emp login on same day(some emp logs off in between and log in again, so we need to track how many attempts were done of Login/Logout.
Late/Early : if emp is late to work. It should be Late, if early it should be early, if he is on time. It should be On Time, if it's Week off then WOFF, if Leave then Leave.
Late/Early by : How many minutes login is delayed, or how many minutes he logged in early. If on time, week off or leave it would be 0.
Target Min : What are the target min to login for that day.(earlier post)
If he is doing 1 Aug 2300 - 0800 shift his login target would be 60 min as date will change post 00:00 hours.
If agent has week off on 2 Aug, his login target would be 480 min (previous shift).
Staff Time Min : How many min was the agent logged in.
Staff time Completed : if emp/agent completes his staff time as per the login time, it's yes, if no its No, Week off/Leave will go as blank.
Schedule Adherence % : it a formula(Staff time min/Target min)
 
1) Login Target is 540min, 480min, 60min...
Do You know where would that come? What would it mean?
If Aug 2 ... but ...If Aug 1 'night shift' (23-08) and Aug 2 same shift (23-08) ... means 9hr work ... means ... 480min or 540min ... or what?

2) Okay, if employee don't follow 'Roster' ... no matter!
Anyway, 'Roster' won't follow employee!

6) Duration: ...seconds and gotta convert ...
I hope that nobody use those ... in any calculations!
Should those present something duration of current event? or what?
Screen Shot 2017-07-29 at 15.25.17.png
I check few 'seconds' and there are 'some' differences.
ex 16/07/2017 12:07 3.Coaching = 9sec !Yes!
prev stamp is 10:55 ... ~1hr12min ... something no match?
If final output will create with ... wrong values ...
What would it mean/happen?

7) Terms:
No of login attempts: ... two 'night shifts' in one day?
... means working 00-08 and 23-24 ... two parts but one day?
Target Min: Could You highlite where did You tried to open this?
Staff Time Min: If no logout event ...? That has been so so common with these!
Staff time Completed: ... some times are 'Target' ... now 'Staff' ...
if those are different then ... okay ... but if same ... then why two terms?
 
Vletm, I am glad you are trying to help me.
I agree to the fact that it could be a little complicated for you due to 24 hours Rotational Shifts.

I have attached a file with detailed explanation with example, so that we both can be on the same page.
Let's go one by one.

The file updated is related to Roster and Staff Time query you had.
Once this I'd cleared we will move on yo next.

Just yo let you know that Staff time means the number of minutes emp has logged in to Cisco tool yo take calls.

Before you open the file assume that this is the first day when the company started.
 

Attachments

  • Staff Time.xlsx
    9.7 KB · Views: 6
I agree to the fact that it could be a little complicated for you due to 24 hours Rotational Shifts.
I'm not! This isn't the 1st time to do this kind of case!
There are no challenge if I could get facts which would work with all cases!
This looks one of simplest; breaks include 'working'.
That new file is okay then it shows the first days of Company.
But in real life, it have to know previous days 'shift'.
That has been the challenge before this! As You would understand.
'Terms' are 'terms' which should know what do those mean.
... There should use term which can know ... not guess (normally wrong way!)
 
I hope I cleared all your doubt about Staff Time and Staff Time required.
Now let me tell you how we calculate.
In previous file we assumed that its the first day of the company
Staff time is calculated from 1 to 30,31 of the month
What we do is, that we remove the data from 1st of the month.
We manually delete all entries for people who logged in Yesterday.
Hope 1 concept is now cleared.
 
Status
Not open for further replies.
Back
Top