Employees attendance tracking template

[y3kesprit]

Hi all,

I am trying to create a tracking template for my company of about 30 employees.

How I intend it to work is to have the first spreadsheet that contains information about these 30 employees such as their employee-ID, name, department.

Spreadsheet 1: Containing information about all employees that I want to track:

Spreadsheet 2: work days

On this second spreadsheet titled Workdays,
employees who comes to work will select cell A3 and use a bar code scanner to scan his card (cell A3 might change depending on my final layout). The barcode scanner will read for the unique employee ID, and B3 and C3 will automatically populate by looking up the unique ID on the Spreadsheet 1.

Now comes the tricky part which is to do the recording of time that they report for work and leave for work.

The employees usually report for work on a Monday, for example, and they will leave two days later (Wednesday). Is it possible for Excel to automatically record the date and time that Mary reports for work on D3 whenever she does the first scan. And when she scans again on A3 when she leaves work, E3 will be the next to fill up. Now that might be a possibility that she might return back to work again during that workweek. Thus when she scans again, now F3 will record the second time she reports for work, and G3 the second time she leaves for work. (Ideally, I hope there can be no restriction in the number of times an employee can scan in and scan out, but if that's a problem, 5 times will be fine)

The second part I want to know is whether it is possible to write a code such that the main spreadsheet can automatically capture the date and time that the employee checks in and out for work and automatically populate the respective cells of the work days. For example, Mary checks in on a Wednesday and leaves on a Friday, the main spreadsheet (first screen shot) can automatically pick this up and populate Wed and Fri as well as the time she leaves accordingly. This way, I can tell at glance through the first spreadsheet, which day the employee was present in the office.

I am currently using Excel 2013.

Sorry that I had such a long post. If there is any difficulty in understanding, I will be prompt in clarifying.

Thank you so much for your help!

 

[y3kesprit]

Hi y3kesprit,



I just did Monday as a test to see what you thought about it. Redoing the code for the other days is no problem.

Regarding the Master list sheet.

It appears the check in's and the check out's are not your typical "8 to 5" work days. Does a worker check in for a few days then check out?

With your A11 example Tuesday to Thursday. Will there be in and out between those two days, and if so what do we do with them? Or does that NOT happen.

I need a general rule of what does go to the master and what does not go to the master.

Howard

I would say there are no general rules when an employee will check in and out. Typically, they will come in on a Sunday night and leave on a Friday evening. That's the trend of the majority. However, there are employees who might leave during the workdays for reasons like medical leave, training etc. For medical leave, they usually won't come back for one or two days depending on the length of the medical leave. For training, they will leave half a day, and come back again.

Yup that's about it. Not sure if that is what you were asking for.

 

[L. Howard]

So for say master list, Monday, employee A11 you want the first check in and the latest check out? And disregard all in/out's between those two times?

On the week day sheets you have five check in/out's cells and on master list you can only record two. Since dates are just numbers, for example the date and time I am typing this is 41784.9635130787.

Therefore I'm thinking for the scan in on master use the SMALL function referring to scan in cells on the week day sheets and the LARGE function referring to the scan out cells.

Does that seem to fit you thoughts?

Howard

 

[y3kesprit]

So for say master list, Monday, employee A11 you want the first check in and the latest check out? And disregard all in/out's between those two times?

On the week day sheets you have five check in/out's cells and on master list you can only record two. Since dates are just numbers, for example the date and time I am typing this is 41784.9635130787.

Therefore I'm thinking for the scan in on master use the SMALL function referring to scan in cells on the week day sheets and the LARGE function referring to the scan out cells.

Does that seem to fit you thoughts?

Howard

I do not want to disregard actually… Cause I want to keep track of every employee’s movement in and out of office. Sorry the two cells under each day are misleading. I would increase it more to 4 cells per day. I guess the maximum times an employee can come in are two times. Employee A11 come in once, leaves, and comes back and leave again. Maximum of two cycles.

I guess the small and large function will not be applicable if I want to keep track of all the times across each day and the whole week?

 

[y3kesprit]

I guess I can just do a simple equation to equal the cells in master list to that of the respective work days. Basically make it so that whatever appear for the Mondays will appear under the Monday column on master list... Simple enough, but I guess it will work.

Howard, is it possible for a pop-up error to appear whenever the scanned barcode doesn’t appear in the list? For example we have from A11 to A30. If a A31 tries to scan, could we have an pop up that says “Sorry, your ID is not recognized in the sytem.”.

 

[L. Howard]

Okay, that's a new twist.

SMALL and LARGE may still work, for instances:

=SMALL(range,1) and =SMALL(range,2) would return the two check in's, LARGE 1, and 2 would return the two check outs.

Sound okay?

Howard

 

[y3kesprit]

Okay, that's a new twist.

SMALL and LARGE may still work, for instances:

=SMALL(range,1) and =SMALL(range,2) would return the two check in's, LARGE 1, and 2 would return the two check outs.

Sound okay?

Howard

Sounds fine. I played around with the function I guess it will work

 

[L. Howard]

I guess I can just do a simple equation to equal the cells in master list to that of the respective work days. Basically make it so that whatever appear for the Mondays will appear under the Monday column on master list... Simple enough, but I guess it will work.

Howard, is it possible for a pop-up error to appear whenever the scanned barcode doesn’t appear in the list? For example we have from A11 to A30. If a A31 tries to scan, could we have an pop up that says “Sorry, your ID is not recognized in the sytem.”.

I believe that can be done, will get to it right after current alligator is subdued.

Howard

 

[y3kesprit]

I believe that can be done, will get to it right after current alligator is subdued.

Howard

Okay thanks!

 

[L. Howard]

Hi y3kesprit,

Here is a version for you to try.

Bound to be a few thing that need tweaking or changed.

Wring it out and see what you like and what you don't like.


https://www.dropbox.com/s/4fd68b0xyvl3pjw/Employee Attendance Tracker Drop Box.xlsm

Regards,
Howard

 

[y3kesprit]

Hi y3kesprit,

Here is a version for you to try.

Bound to be a few thing that need tweaking or changed.

Wring it out and see what you like and what you don't like.


https://www.dropbox.com/s/4fd68b0xyvl3pjw/Employee Attendance Tracker Drop Box.xlsm

Regards,
Howard

Hi Howard,

Thanks for getting back to this project!

I have tried what you edited. The pop-up box for wrongful entry is great!

I have a few questions:

1. What do I do if I intend to add new columns and rows? For now, I would like to add one extra column on the extreme left for ‘serial number’. If I do that, the times will appear wrongly. As I refine the template, more columns and rows will go into it. Is there any specific part in the vba that pertains to this?
2. Are you able to point out if there is a way for me to conditional format in such a way that when an employee checks in, the entire row of his name will become green-filled cells, and when he checks out, it becomes red-filled cells? (i.e, the moment A11 comes in, and scans in, his row becomes green, indicating he has reported for work. When he leaves subsequently, it becomes red. And (if ever) he comes back in the same workweek, it becomes green again, and so on.
3. I also intend to do a drop down menu on the master list, so that I can indicate the reasons our employees leave office. This is because, unlike checking in, there are a multitude of reasons to leave office such as meeting clients, going on a sick leave etc. How do you suggest that I integrate both the times and reasons within ‘master list’ without appearing too messy.

Thanks for your help! Looking forward to hear from you!