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!

 

[L. Howard]

Give this a try, I think I got it going like you want.

https://www.dropbox.com/s/oo7db5lz9fks00j/EMPLOYEE attendance final format Drop Box.xlsm

Howard

 

[y3kesprit]

Give this a try, I think I got it going like you want.

https://www.dropbox.com/s/oo7db5lz9fks00j/EMPLOYEE attendance final format Drop Box.xlsm

Howard

I just took a look at it! It is indeed what I wanted.

This would be great for weekly tracking. Thank you so much for your help!

I posted another variant due to additional request from my supervisor who wants it in a monthly format as well... Guess the bosses always want to look at the bigger picture. Would appreciate if you can take a look at it, though I think I'm asking for too much!

Thanks anyway for your help regardless

 

[L. Howard]

Just to add, the workbook will most likely throw an error here and there due to one reason or other. Using a scanner will skip past most of the errors I caused while running tests.

So it you get an error and the sheet stops working it is most likely fixable by going into the sheet module on the sheet you were working on and at the top of each weekday sheet in a little snippet of code, see it below.

Because I have enable events in the Change_Event code at false at the beginning of each sheets code and then true at the end, if the code throws an error, then the sheet is left with enable events at false. Next time the code tries to run nothing will happen because the sheet was NOT set back to enable events at the end of the code.

If that happens you will need to remember to go into the vb editor put the cursor anywhere inside that Sub XX code and then f5 to run it. There is also a very small green arrow facing to the right that you can click on to run the code. Hover your cursor over the icons and you can read what they do. The little arrow says Run Sub/User Form f5.

I think there is an error catcher routine to prevent having to do this but at this time I don't remember how it goes.

If I find it out I'll pass it on.

Sub xx()
Application.EnableEvents = True
End Sub

Howard