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.

How to track employee time in excel using Macro

Discussion in 'VBA Macros' started by Akash kothari, Apr 14, 2017.

  1. Akash kothari

    Akash kothari Member

    Messages:
    69
    Hi,

    How can we track daily time of employee in excel. (Task1, Task2, Breaks, Meeting, Away from system - if system is idle for like 10 min.

    I have attached a excel file and points which mentioned I can explain, this file I got through some website.

    1) Login and Logout time - Excel should automatically start recording login and logout time as soon as employee opens this file.
    2) Activity - These are the daily activity which employees normally do in office. (Can be manually done)
    3) Project - Project on which employee is working.(Can be manually done)
    4) Start Time / End Time - Start time and End time for the task (need to automate, as soon as employee clicks on start recording button and stop recording button)
    5) Idle time - how long employee was idle on system, for each tasks (System Idle for 5 min, while working on each individual tasks). (need to automate)
    6) Total activity time - is nothing but End time - Start time. (need to automate)

    as soon as employee click on Stop timer that Task row details should get copied to 2nd tab with all the details 'weekly Production Log'.

    Thank you
    Akash

    Attached Files:

  2. vletm

    vletm Well-Known Member

    Messages:
    2,763

    Attached Files:

  3. Akash kothari

    Akash kothari Member

    Messages:
    69
    Thank you so much for your Help Vletm,
    and Sorry for delayed response,

    Do you have any suggestion How can I improve this format ?
    if you have seen any format in past ?

    Thank you
    Akash
  4. vletm

    vletm Well-Known Member

    Messages:
    2,763
    Akash kothari - how to improve ..
    1) You should know what do You want to measure?
    2) .. as well as what someone wants for report ... or just collect data?
    3) 'Idel'-time ... means extra clicks while do real work or why this?
    4) Do every employee have own 'track'-file?
    5) Using this should be much 'clear'; max two clicks per action.
    > At least those, You should figure before next steps!
    Akash kothari likes this.
  5. Akash kothari

    Akash kothari Member

    Messages:
    69
    Hi vletm,

    1) You should know what do You want to measure? - (I am looking to prepare file which track time on daily basis of every employee on different activities, so that will help us to know how much each employee utilized and who has availability)
    2) .. as well as what someone wants for report ... or just collect data?
    (This will be helpful to check utilization of employee's and availability)

    3) 'Idel'-time ... means extra clicks while do real work or why this? (By Idle time I mean if employee has not even moved a mouse for 5 min or more, or when any there are no activity/Task on which he is currently working on)

    4) Do every employee have own 'track'-file? (No, we do not have one as of now, this file should be used to understand how much time a employee spending on which activity)
    5) Using this should be much 'clear'; max two clicks per action.
    (can't we add button of start and stop for an activity - as soon as employee start task he needs to click button and when he is finished should click on stop button and activity reports total time, Idle time and mentioned task gets updated as completed).

    Thank you
    Akash
  6. vletm

    vletm Well-Known Member

    Messages:
    2,763
    Akash kothari ... hmm?
    Would You try use this?
    User can click ONLY those 'Text Boxes' as user changes task/job/duty...
    Green values shows 'working' times and red values idel times.
    Daily values will save.
    > Ideas ... Questions?
    >> Ps. This is not ready!

    Attached Files:

    Akash kothari likes this.
  7. vletm

    vletm Well-Known Member

    Messages:
    2,763
    Akash kothari
    ... next version
    Clicking 'Graph' keeps user 'Alive' = no Idel time!

    Attached Files:

  8. Akash kothari

    Akash kothari Member

    Messages:
    69
    Hi Vletm,

    Amazing, Thank you for making this it looks really Nice.

    Just few more thing,

    I wanted to use data validation on for Tasks Row, so the user can choose accordingly on the task he or she is working, as there could be multiple tea break or Multiple task he or she might be working on so a data validation option I was was trying to put there so the user can use drop down to select his task,but I am not able to edit on that sheet 1.

    1) Can we extend task row further to at least 20 columns ?

    2) can we use Data validation to use for the task or other activity that user is working and he or she can select himself?

    3) 2 buttons as 'Start' and ' Stop' so if any particular project a user is working he can choose start and stop option accordingly, once clicked on stop button task marked gets marked as completed, and if user is not working on any other task all time should go to Idle time.

    4) once clicked 'stop Button' a user should not be able to go back to same task again as task marked as completed, he need to use a different column if need to work on task 1 or project 1 again.

    5) and one refresh button so that all the log time details get cleared on all the tab.

    I have mentioned the data validation list in 4th tab (that can be extended later)

    And Again Apologies for replying late.

    Thank you Akash

    Attached Files:

  9. vletm

    vletm Well-Known Member

    Messages:
    2,763
    Akash kothari ... no - keep this simple - no extra buttons!
    This start tracking then file opens (=idel).
    Then start to do something then select it.
    Then ready/change task then select next task.
    Like if do Task1 and goes to Lunch then
    click 'Lunch' and then come back click task which continues to do.
    Now, it's possible to select nine 'tasks' (from 30 preset) for use in time.
    There is 'no multi-talent-employees'
    (like can be lunch and tea break in same time).

    Attached Files:

  10. Akash kothari

    Akash kothari Member

    Messages:
    69
    Hi Vletm,

    Its giving error, see attached.

    Attached Files:

  11. vletm

    vletm Well-Known Member

    Messages:
    2,763
    Akash kothari
    ... and You didn't do nothing before that?
    'One possible' solution is that my time and Your times has gap ...
    and then this tries to calculate negative times (=not good)
    1) Go to 'tmp'-sheet and clear L3-cell.
    2) Close file
    3) Open file again
    Akash kothari likes this.
  12. Akash kothari

    Akash kothari Member

    Messages:
    69
    Hi Vletm,

    Thank you for your time and efforts, :)
    this file looks very good now and just with few modification I can use it.
    Sorry for any trouble if I may have caused.

    Thank you
    Akash

Share This Page