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.

Break Time calculation( in- out)

Discussion in 'Ask an Excel Question' started by Dinu Kunjunju, Mar 22, 2018.

  1. Dinu Kunjunju

    Dinu Kunjunju New Member

    Messages:
    23
    Folks

    i have a swipe report in excel and i want to calculate the break time for each person. i tried to fix this through referring our blogs but i am not able to fix it. somebody please help me to fix this issue.

    Attached Files:

  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    See if this is OK.

    Narayan

    Attached Files:

  3. Dinu Kunjunju

    Dinu Kunjunju New Member

    Messages:
    23
    Hello Narayan

    Thanks for the update. its really help full. I would check one thing with you as I need to exclude the first entry and last exit from a date for a particular date and the rest of the entry exit will calculate the break time. I am seeing few persons are taking 2 more hours I think bcz of this.
  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    Can you point out one or two specific cases so that I can check it out ?

    Narayan
  5. Dinu Kunjunju

    Dinu Kunjunju New Member

    Messages:
    23
    Hello Narayan

    A Safe example is Please select Card holder as T K, Dinu and select date 12th of March you can see the first time is 11.0.10 Am actually this was he entered in office first time that day. so this should be excluded from break time calculation. then 8.26.54 PM he is left from office and his shift is finished so this time also excluded rest exit and entry will be added into the break time. hope this make sense.
  6. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    Will this apply to all cardholders ?

    Will it apply for every date ?

    Suppose a cardholder has only one entry and one exit on any particular date ; will both of these be excluded ?

    Narayan
  7. vletm

    vletm Excel Ninja

    Messages:
    3,876
    Dinu Kunjunju
    Here is one sample for You.
    You can see daily 'break times'
    and comments shows ALL Entry & Exit times.
    If they miss to ENTRY or EXIT -
    then there would be challenge or
    if they work night shift.

    Press [Do It] to see results.

    Attached Files:

  8. vletm

    vletm Excel Ninja

    Messages:
    3,876
    Dinu Kunjunju
    I added few features, cause eg #5 Reply.
    [Calc Breaks] do as name says
    [Show Name] You can select any name after [Calc Breaks] to see its all rows
    ... back to 'normal view' with ...
    [All Rows] shows all rows
    [ReSet Hides] as there are 'sometimes extra row'
    which You have marked as 'hide' and
    if You would like to 'forget' ALL of those, press [ReSet Hides].
    'Hiding':
    Highlight any row Device-cell by any 'Fill Color'.
    After that row won't notice while NEXT [Calc Breaks].
    That 'mark' will show by yellow 'Fill Color'-cell in results.

    Attached Files:

  9. Dinu Kunjunju

    Dinu Kunjunju New Member

    Messages:
    23
    Hi Narayan

    Yes this for all date, if one person have single log in and log out you can exclude them bcz they are security or other staff not the employees reporting to me. from this report I will sort out the persons who reporting to me.

    Regards,

    Dinu Kunjunju
  10. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    Since vletm is also involved in giving you a macro solution , do you still want a formula-based solution ?

    Narayan
    ThrottleWorks likes this.
  11. Dinu Kunjunju

    Dinu Kunjunju New Member

    Messages:
    23
    Hi Narayn

    Yes, I saw that one but I am not sure all exit and entry calculated apart from first log in and last log out. bcz I noticed that few of my staff are only take 25 minutes as per the report that's wrong. that's what I confused.
  12. vletm

    vletm Excel Ninja

    Messages:
    3,876
    Dinu Kunjunju You wrote:
    but I am not sure all exit and entry calculated apart from first log in and last log out. bcz I noticed that few of my staff are only take 25 minutes as per the report that's wrong. that's what I confused.
    Did You do ... [Show Name] You can select any name after ...?
    Did You checked those BreakTime-comments? There should be ALL logs!
    You can SEE all that names rows from Your data!
    This only tries to show what do You data have.
  13. Dinu Kunjunju

    Dinu Kunjunju New Member

    Messages:
    23
    Hello

    Thanks for the detailed explanation, for a safe example after running the macro the user Antony Dinil got break time 3 minutes on 16th March 2018, I calculated it manually and the actual break time is around 1 hour. this is what my concern with it.
  14. vletm

    vletm Excel Ninja

    Messages:
    3,876
    Dinu Kunjunju
    Okay ... good note!
    I checked and I found typo ... it show only the last 'Break'
    16-Mar-2016 - Antony Dinil
    Entry - Exit times: 6:28:40
    Exit - Entry times: 2:20:57 Total: 8:49:37
    Do those values 'look better'?

    ... I don't know Your around 1 hour ...
    ... and It's possible to get both ... times as shown above.

    Ps. It's always wise to check some values manually!

    Attached Files:

  15. Dinu Kunjunju

    Dinu Kunjunju New Member

    Messages:
    23
    Hello

    I have calculated manually the break time for Antony Dinil and I am getting 2.20.57 on 16th March. here is the way I calculated the break time. I consider the exit and entry marked in red.

    09:17:00 PMKOCHI CARNIVAL 1F INVISTA EXITAccess Granted30115822104387ANTONY, DINIL
    07:1:06 PMKOCHI CARNIVAL 1F INVISTA ENTRYAccess Granted30115822104387ANTONY, DINIL01:16:54
    05:44:12 PMKOCHI CARNIVAL 1F INVISTA EXITAccess Granted30115822104387ANTONY, DINIL
    04:4:51 PMKOCHI CARNIVAL 1F INVISTA ENTRYAccess Granted30115822104387ANTONY, DINIL00:09:14
    03:55:37 PMKOCHI CARNIVAL 1F INVISTA EXITAccess Granted30115822104387ANTONY, DINIL
    02:49:49 PMKOCHI CARNIVAL 1F INVISTA ENTRYAccess Granted30115822104387ANTONY, DINIL00:51:25
    01:58:24 PMKOCHI CARNIVAL 1F INVISTA EXITAccess Granted30115822104387ANTONY, DINIL
    12:45:12 PMKOCHI CARNIVAL 1F INVISTA ENTRYAccess Granted30115822104387ANTONY, DINIL
    12:41:48 PMKOCHI CARNIVAL 1F INVISTA EXITAccess Granted30115822104387ANTONY, DINIL00:03:24
    12:27:23 PMKOCHI CARNIVAL 1F INVISTA ENTRYAccess Granted30115822104387ANTONY, DINIL
    02:20:57

    Attached Files:

  16. vletm

    vletm Excel Ninja

    Messages:
    3,876
    Dinu Kunjunju
    As I wrote .. verified my Break Time is 2:20:57
    And it seems to be same as Your the latest Break Time value!
    So, my version gives same values as You're looking for - or how?
  17. Dinu Kunjunju

    Dinu Kunjunju New Member

    Messages:
    23
    Yes, but what I am confused here is the total break time for Dinil 2.20 is only for one day not entire day. in your file each breaks are showing under different dates. correct me if I am wrong.
  18. vletm

    vletm Excel Ninja

    Messages:
    3,876
    Dinu Kunjunju
    Those are daily values as You could see!
    Screen Shot 2018-03-27 at 15.38.16.png
    But if it needs only ONE value ... not a challenge
    You can get both!
  19. Dinu Kunjunju

    Dinu Kunjunju New Member

    Messages:
    23
    Now its correct, can you share me this file, that would be really greatful
  20. Dinu Kunjunju

    Dinu Kunjunju New Member

    Messages:
    23
    The current file which you shared is showing different data.
  21. vletm

    vletm Excel Ninja

    Messages:
    3,876
    Dinu Kunjunju
    That my previous sent file (#14) has output like (#18).
    Now uploaded file has those Totals too.
    I don't know which file ... which data You're writing.
    I've used all the time data from Your (#1) the 1st message.

    Attached Files:

  22. Dinu Kunjunju

    Dinu Kunjunju New Member

    Messages:
    23
    Thank you so much it is working fine. if I am getting new file then can I delete the existing data and replace it that will work right.
  23. vletm

    vletm Excel Ninja

    Messages:
    3,876
    Dinu Kunjunju
    Who would prevent that?
    Take care that layout is same as this,
    then it would work.
  24. Dinu Kunjunju

    Dinu Kunjunju New Member

    Messages:
    23
    Hello

    The report is same, we receive this file every week with same format.

    Thanks.
  25. Dinu Kunjunju

    Dinu Kunjunju New Member

    Messages:
    23
    Hello Can you please help me with one question. I need one more feature is added to be this macro file. i have to show the Total Working hours along with the break time. that would be calculated from the first log in and last log out. then only i can analyze the exact break timings. we have total 9.30 working hours and i gave 1.30 breaking hours to staff. i want check if any one is logging early then then can adjust with breaks.

Share This Page