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!
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!
