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.

Time Tracker For Team Members

Discussion in 'VBA Macros' started by Abhishek A, Nov 2, 2018.

  1. Abhishek A

    Abhishek A New Member

    Messages:
    19
    1.When User Enter the Employee ID in Sheet 1 Page, it should fetch the name from "Sheet 2"(Data). More people will be added later
    2. When LOGIN Button is pressed - it should display the Time (Once Login is pressed - Log In Button should be disabled)
    3. When LOGOUT Button is pressed - it should display the Time (Once LogOut is pressed - Log Out Button should be disabled)
    4. YOUR TOTAL LOGIN HRS FOR TODAY IS - when LOGOUT is pressed, it should display total hours spend on that day.
    (NOTE: Other than Employee ID Text Box, all other Items should be disabled for editing for user)
    5. Once Log In is pressed - Current Date, Employee Name, Login Time sould be saved in Report sheet. (Should not be editable)
    6. Once Log Out is pressed - it should display the time in Logout Column. (Should not be editable)7. If there is no Log in and log out it should mark in the report as Leave

    8. If the total time is less than 7 hrs then it should take it as half day in the report.


    If you have a ready templet or you have better ideas that’s also work fine for me.

    Attached Files:

  2. vletm

    vletm Excel Ninja

    Messages:
    4,299
    Abhishek A ... other idea:

    Why needs to write any ID somewhere?

    LOGIN
    1) Select 'own name' from display
    2) Get 'Login'-time
    LOGOUT
    3) Select 'own name' from display
    4) Get 'LogOut'-time

    Those act will save and use later.
    Of course, all You named features won't be ready in this sample.
    Now, those 'Names' are as sample names.

    > Questions?

    Attached Files:

  3. Abhishek A

    Abhishek A New Member

    Messages:
    19
    Problem:-
    1. i have a team of 80 and the count will go up. so i will have the emp name and ID in the separate sheet.
    2. I looking this tracker to help in reporting things like, Hrs worked, leave, half day etc
    3. And i dont want people to do proxy login logout
  4. vletm

    vletm Excel Ninja

    Messages:
    4,299
    Abhishek A
    What 'P...' word ... do You mean challenge?
    1) There can add as many names as need.
    .. and same time could see, who is IN or OUT.
    2) Those can be there, You know when IN/OUT...
    3) Why need to try enter something, many times misspell?
    ... find own name and 'Click'
    ... Which would be quicker?
    ... and Something gotta do
  5. Abhishek A

    Abhishek A New Member

    Messages:
    19
    Challenges:-
    1. We dont have same day login and logout. Ex, login - 11/02/2018 05:00 PM and logout 11/03/2018 02:00 AM
    2. How to clock leave and half day
    3. What about the report with the details. And the report should be editable by only one person(Me)
  6. vletm

    vletm Excel Ninja

    Messages:
    4,299
    Abhishek A
    1) As written:
    ... then someone goes IN then 'click' and save that moment
    ... then someone goes OUT then 'click' and save that moment
    Is it matter when those times has done?
    2) There could add eg 'button' for 'LEAVE'
    Of course, You'll have clear rules ... half day ... and so on!
    3) All details should made just for this case.
    Why needs to edit something - the truth based from 'clicks'.
    You could get reports out.
  7. Abhishek A

    Abhishek A New Member

    Messages:
    19
    Okay.. Can you add the leave button and i am not good at macros so changing the name button to particular name will be challenging from my end.
    1. Once we have the template can we add/make changes to the same ?
  8. vletm

    vletm Excel Ninja

    Messages:
    4,299
    Abhishek A
    ... even this won't be one minute job.
    You should write clear Your needs.
  9. Abhishek A

    Abhishek A New Member

    Messages:
    19
    Log in time, Log out time, Total hrs, Leave and report. This details will be enough
  10. vletm

    vletm Excel Ninja

    Messages:
    4,299
    Abhishek A
    What kind of 'Total hrs'?
    What kind of 'Report'?
  11. Abhishek A

    Abhishek A New Member

    Messages:
    19
    Hey, I found one of your previous work on the same topic "Need help in creating Login- Logout time tracking macro". My requirement is pretty much same in that. Can we use the same and develop for my requirements? I hope it will save more time
  12. vletm

    vletm Excel Ninja

    Messages:
    4,299
    Abhishek A
    Every case normally has of specific details.
    Try to test this and get ideas ...
    1) make own folder to this file
    2) copy EmpTime2.xlsb in prev folder
    3) create folder named backup in previous folder
    4) open EmpTime2.xlsb
    5) There is [ Set ]-button (Right Top) - press it and write PassWord
    6) Plan how to fill (order) Names and Fill those.
    7) Note! coord has comment - do as it 'says'
    8) Press [ Back ]
    9) Test-test-test-test to use 'Basic-view'
    10) There are some settings from 'previous case' and so on ...
    11) Need to know exact 'what needs?'

    Attached Files:

  13. Abhishek A

    Abhishek A New Member

    Messages:
    19
    Saved in the folders and added names.
    1. Its popping up the names till coord 47 if add 51 its showing complete blank in basic view.

    Attached Files:

  14. vletm

    vletm Excel Ninja

    Messages:
    4,299
    Abhishek A
    That really took ~40hrs to notice?
    I asked to fill those 'Names' ... not copy paste, that's different!
    As You also remember, this is quick copy of someone else case.
    There are still 'old settings' ... many of those!
    Those have to modify to work with this case.
    There were eg only day shifts and few limits.
    That's why, I've asked:
    What really needs?
    Clear rules for all of those.
    All those have to know!
    ... and of course, night shift hour have to split to to days!
    Is it daily report or what?
    How You have plan to use this kind?
    > PS copy that Emp_tmp.xlsb also to backup-folder.

    Attached Files:

  15. Abhishek A

    Abhishek A New Member

    Messages:
    19
    I am sorry I don’t work on weekends and my shift starts from 5 PM.

    Okay I will fill the names. What about if i need to insert more can i just follow the sequence..

    What really needs?

    1. Login date and time, Logout date and time, Total hrs worked (Logout – login)

    2. Leave tab

    3. It’s a daily activity, People will login and logout on daily basis. But report I need for weekly and monthly.
    4. EmpTime2 is not closing. I have to go to task manager everytime to close the list
  16. vletm

    vletm Excel Ninja

    Messages:
    4,299
    Abhishek A
    Do this need to work in weekends?
    .. and what would be that areas weekend?
    You can fill ( not use traditional copy & paste! ) ~150 names (+coord + ID).
    >> Use new file! <<
    What would mean 'Leave tab'?
    Daily activity means split if shift goes over midnight ... yes?
    How long is max allowable working hour per shift?
    ... because sometimes someone would miss to LogOut!
    What kind of reports? Any ideas of layouts?
    4) What mean 'is not closing'? ... File > Close or Excel > Quit...
    eg missed answer:
    How You have plan to use this kind?
    > All needed details/rules have to know before can modify code! <
  17. Abhishek A

    Abhishek A New Member

    Messages:
    19
    1.Weekend its not needed. Weekend i.e Saturday and Sunday.

    2. Basic view is working fine. I am able to add names and 150 is more than enough

    3. Leave – when person take off from work.. Did not show up to office.

    4. Daily activity means split if shift goes over midnight ... yes. It does go till 7AM

    5. Max allowed shift is 10hrs.. After this can logout automatically

    6. Shifts.. There are 3 shifts a) Day shift b) Mid day shift c) night shift

    All shifts max working hrs is 10hrs

    Day shift – 10AM to 7PM (Same Day) > Login b/w 10 to 1

    Mid day shift - 1PM to 10PM (Same Day) > Login b/w 1 to 12

    Night shift – 5PM to 2AM (Next Day)> Login b/w 5 to 7

    7. Closing and opening is working fine now
    8. Report something like attahed

    Attached Files:

    Last edited: Nov 5, 2018
  18. vletm

    vletm Excel Ninja

    Messages:
    4,299
    1) Means, that there are no night shift in Fridays (after midnight ... no hrs) ?
    nor from Sunday to Monday?
    3) Before LogOut member select [Leave] and after that
    ... member will have 'Leave' ... until next [LogIn] ... or how?
    4) hmm? Night Shift ends ~2AM max 5AM (7PM+10hrs) ... how until 7AM?
    5) means, shift can be max 10hrs
    6) if DayShift LogIn before 10AM ... LogIn time will be 10AM ... okay?
    ... if later than 1PM ... hmm?
    same with MidDayShift ... but how possible b/w 1 to 12?
    8) Report notes:
    All 80 employees in same report?
    There are many 'white cells'?
    None NightShift? ... I found ... but hours are solved 'wrong way'?
    Cell A36 ? without rules?
    Cell A37 ? without rules?
    That looks like a plan? That don't show real LogIns.. LogOuts!
    and those LogIns and LogOuts no need to show dates!

    Third time:
    How You have plan to use this kind?
  19. Abhishek A

    Abhishek A New Member

    Messages:
    19
    1.Firday’s does have night shifts. For shift timings on daily basis please see Oct 2018 file. That gives you the better picture about the timings of different people

    3. Leave means there will be no login and log out. People wont come to office that day. In Oct 2018 sheet yellow fields refer leaves

    4. 7AM is the last logout. Few people login around 9PM

    5. Yes. Max working hrs is 10 only.

    6. Let the login be the actual time. Mid day shift Login b/w 1PM to 5PM (Sorry my mistake)

    8. Report :-

    Yes, All employees will be one report.

    White cells refers night shifts. Hrs adjusted manually as we don’t have any automated and actual time

    Ignore Cell A37(OT) and A36(US Holiday) I will update them manually


    How You have plan to use this kind? I will save the file in shared folder and instruct everyone about how to login and logout. For this only one should have access to edit rest all should have the access to only login and logout
  20. vletm

    vletm Excel Ninja

    Messages:
    4,299
    1) I just noticed that Night Shifts has calculated wrong way!
    I need and follow rules and those should use too.
    3) Leave ... when / how someone would mark 'Leave' ... or as I wrote?
    NightShift ... means 'morning hrs' ... means no 'Leave' ...okay?
    4. 7AM is the last logout. Few people login around 9PM
    How possible? If max 10hrs per shift! As You verified in (5)? Rules?
    6) Rules? If You name gap when should LogIn then minimum time which can use for calculation could be the minimum time! Of course, real Logtimes will always save!

    Report:
    So no need to matter which shift or how?

    Use:
    OUCH!
    If 30 members use this same time then ... It's no work that way!
    Nice to figure this now ... only after few hrs coding ... yes!
    That's why I ask questions ... which would need answers.

    This would work, if everyone would use this from one computer!

    'Your way' means that everyone should have own file to use
    eg like below sample
    Screen Shot 2018-11-05 at 22.03.38.png
    and
    someway datas would read for You.
    That's possible to do too.

    Is there something more - which would good to know?
  21. Abhishek A

    Abhishek A New Member

    Messages:
    19
    1. Night shift calculations is simple, People who logged in on/after 04:30PM and completed minimum 7Hrs are eligible for night shift.

    2. Leave – Any person is not coming to office due to personal reason. There will no login or logout when a person is on leave.

    3. 10Hrs rules – irrespective of login time max working hrs will be 10 hrs.

    4. Use – Everyone needs their own file and that should be restricted to edit anything apart from login and logout buttons.

    5. Report –

    a. Day shift: - People who login before 12:30 PM and completed 7hrs

    b. Mid-day shift: - People who login on/after 12:30 PM and before 04:30 PM and completed 7 hrs.

    c. Night shift:- People who login On/after 04:30 PM and completed 7hrs

    6. Instead of OWN and WORK we need Login & Log out button and a Leave Button
  22. vletm

    vletm Excel Ninja

    Messages:
    4,299
    1) hmm ... really?
    Is someone LogIn 23:59:59 then how many working hours for begin of shift (before midnight) and how much for next day?

    2) Leave ... so far those [ Leaves ] can make only with LogIn/LogOut.

    3) >10hrs will cut - okay?
    ... many missing answers.

    6) That 'new form' is quick copy from other case!
    There is ONE BUTTON for both LogIn and LogOut.

    I won't use hours before I would know needed details!
    > I rechecked Your set 'Oct 2018'-file .. hmm?
    As I wrote ... there are some which should do other way!
    I did 'plan'-sheet. It's basic as Your 'sheet1'.
    Cell values are 'planned LogIn-times'
    eg 14:00 means LogIn 14:00 (= 2PM) and LogOut 14:00+09:00
    if 'not regular times' as Cell AM32 then right side value show shift length.
    There are also solved 'not regular working hours' with comments.
    You'll notice that in many cases 1st working day's hours would be less than 09:00 ... because ... missing previous days data.
    Also, many Saturdays will have 'hours' because LogOut-time will be Saturday.
    Next Months 1st day also has sometimes already 'working hours' - of course!
    ( 'Regular Working Hours' are left away - it's more clear to read! )
  23. Abhishek A

    Abhishek A New Member

    Messages:
    19
    1) If someone is login at 23:59:59 he must work till 9 AM. As he is login after 04:30 PM he is eligible for night shift. Company standard is person should work 9 hrs.

    2) Leave – On leave days person won’t come to office only. So, there will be no login/logout

    3) One button for login and logout is fine,

    Saturdays will have logout time for night shift people.

    I don’t want to miss anything on 1st working day as well. Bcoz this involves cost to the company
  24. vletm

    vletm Excel Ninja

    Messages:
    4,299
    Abhishek A
    'Member Form'
    Only one member can use it (later)
    = member have to make own Logs.
    This is 'master copy of that form'!

    Attached Files:

  25. Abhishek A

    Abhishek A New Member

    Messages:
    19
    1. Member name will auto pull or each individual needs to enter it manually? Can we have Emp ID and Emp name both. Bcoz we have people with same name

    2. Leave – Emp won’t come to office can I update on behalf of them. Can Emp update leave for future dates?

    3. Format looks simple and handy.

Share This Page