1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'Ask an Excel Question' started by sms2luv, Jul 27, 2017.

Thread Status:
Not open for further replies.
  1. sms2luv

    sms2luv Member

    Messages:
    284
    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
  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,586
    Hi ,

    Can you upload your workbook with enough data in it ?

    Narayan
  3. vletm

    vletm Well-Known Member

    Messages:
    2,800
    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"),"")
    sathishsusa likes this.
  4. sms2luv

    sms2luv Member

    Messages:
    284
    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.

    Attached Files:

  5. vletm

    vletm Well-Known Member

    Messages:
    2,800
    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?
  6. sms2luv

    sms2luv Member

    Messages:
    284
    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.
  7. vletm

    vletm Well-Known Member

    Messages:
    2,800
    sms2luv
    If no 'Login Time' ... is it late or not?
    hmm...?
    Maybe someone missed that day!

    Something like this?
    Press [Do It]

    Attached Files:

    rahulshewale1 likes this.
  8. sms2luv

    sms2luv Member

    Messages:
    284
    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.
  9. vletm

    vletm Well-Known Member

    Messages:
    2,800
    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.
    sms2luv likes this.
  10. sms2luv

    sms2luv Member

    Messages:
    284
    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.
  11. sms2luv

    sms2luv Member

    Messages:
    284
    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.
  12. vletm

    vletm Well-Known Member

    Messages:
    2,800
    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!
    sms2luv likes this.
  13. sms2luv

    sms2luv Member

    Messages:
    284
    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
  14. vletm

    vletm Well-Known Member

    Messages:
    2,800
    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!
    sms2luv likes this.
  15. sms2luv

    sms2luv Member

    Messages:
    284
    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.

    Attached Files:

  16. vletm

    vletm Well-Known Member

    Messages:
    2,800
    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'?
  17. sms2luv

    sms2luv Member

    Messages:
    284
    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.
  18. vletm

    vletm Well-Known Member

    Messages:
    2,800
    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!
  19. sms2luv

    sms2luv Member

    Messages:
    284
    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).

    Attached Files:

  20. vletm

    vletm Well-Known Member

    Messages:
    2,800
    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!
  21. sms2luv

    sms2luv Member

    Messages:
    284
    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)
  22. vletm

    vletm Well-Known Member

    Messages:
    2,800
    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?
  23. sms2luv

    sms2luv Member

    Messages:
    284
    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.

    Attached Files:

  24. vletm

    vletm Well-Known Member

    Messages:
    2,800
    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!)
  25. sms2luv

    sms2luv Member

    Messages:
    284
    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.
Thread Status:
Not open for further replies.

Share This Page