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]

Do this for the time being.

Add all the columns on the you think you will need.
And all the rows you think you will need.
Color fill all the cells where you think you want the drop downs, say red.

Do nothing more.

Then save as "current name" + "Added Columns and Rows" or what ever.

Post a link to the newly altered workbook. With the post explain exactly how many and where all the added rows and columns are and what sheet/s.

And what will the newly added columns and rows be used for?

I can then ponder its fate.

Howard

 

[L. Howard]

I presume you caught the Employee ID Numbers were askew on the master sheet.

That was a product of formatting the sheet with the alternating row shading.

Easy to fix.

Howard

 

[y3kesprit]

Do this for the time being.

Add all the columns on the you think you will need.
And all the rows you think you will need.
Color fill all the cells where you think you want the drop downs, say red.

Do nothing more.

Then save as "current name" + "Added Columns and Rows" or what ever.

Post a link to the newly altered workbook. With the post explain exactly how many and where all the added rows and columns are and what sheet/s.

And what will the newly added columns and rows be used for?

I can then ponder its fate.

Howard

Hi Howard,

Here is the link to the file:

https://www.dropbox.com/s/3n54v280u44waxb/attendance tracking + column A, D and E.xlsm

I have added in three new columns.

Column A: serial number
Column D: info 1
Column E: info 2

I have also expanded the number of rows to fit 100 entries, i.e. S/N = 100

The standard template for the work day is based on Monday. I have tried replicating it to all 7 days but I keep getting some error. Would appreciate if you can help me with that!

Also, I expanded from 5 work days to 7 work days. So the IF formula on ‘master list’ has been over written.

Thanks for your help, Howard!

 

[L. Howard]

Hi y3kesprit,

Okay, I'll take a look. Adding columns this deep into the project will almost certainly be a big make over.

Mean while take a look at the link.

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

Some thing to notice:

Can't sign out until you have a sign in
Can't sign in a second time without a sign out.
Only two each In's & Out's.

Master shows green with a sign in and red with sign out.

On master sheet, note cell A1 is shaded and has the word "Clear". To clear master of the red/green rows, select A1 and over write the word clear with the clear and hit enter. Colors are gone and the word Clear is re-instated.

Howard

 

[y3kesprit]

Hi y3kesprit,

Okay, I'll take a look. Adding columns this deep into the project will almost certainly be a big make over.

Mean while take a look at the link.

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

Some thing to notice:

Can't sign out until you have a sign in
Can't sign in a second time without a sign out.
Only two each In's & Out's.

Master shows green with a sign in and red with sign out.

On master sheet, note cell A1 is shaded and has the word "Clear". To clear master of the red/green rows, select A1 and over write the word clear with the clear and hit enter. Colors are gone and the word Clear is re-instated.

Howard

Hi Howard,

I tested the new features.

I have one problem, I can’t sign in and out on different days.

For example if I sign in on Monday, when I try to sign out on Friday, the error message will pop out.


I have a final request.

Previously, the time will be reflected accordingly on the master list according to the days that an employee signs in. For example, A11 signs in on Monday at 8am, and this will be shown on the master list accordingly.

Is it possible to revamp the form of presentation, such that the timings need not be shown on the master list. This is so that I can replace the time with the drop down menu.

I have uploaded the new format I hope to achieve in the link below. As you can see, the entire columns have been highlighted red meaning that entire column will be drop down menus.

When an employee signs in, the time need not be shown anymore. Instead of that, I will make do with coloured cells. So for example, A11 signs in on a Monday, instead of the time reflected, the cell will just turn green. And the cell will be filled with an option based on the drop down menu.


Also, for the coloured formatting, is it possible for the green cells to only be highlighted on the day that an employee has signed in and onwards? For exmaple, if A11 checks in on a Wednesday, only Wednesday onwards will be green. And when he checks out on Friday, Wed to Fri will become red. This would make it easier for me to have a visual tracking based on the colours.

I hope I didn’t confuse you too much.

Here is the link:
https://www.dropbox.com/s/m56724v2iv3gjh5/final format.xlsm

I am terribly sorry for not providing the finalized format in the first place, and causing inconvenience now. Anyway the link I sent you here is the final format, and I don't foresee any more changes.

Thank you so much for your help!

 

[y3kesprit]

Hi the link I posted in the previous reply is the final format. Please refer to that, Howard. Thank you!

 

[y3kesprit]

Hi the link I posted in the previous reply is the final format. Please refer to that, Howard. Thank you!

For the highlighting of the cells, is it possible to do it such a way that the cells will only highlight from the days that an employee checks in. For example, A11 checks in on Wed, Wed onwards will be highlighted green instead of the entire stretch. And when he checks out on a Friday, Wednesday to Friday will become red. This will provide a better visual indication for the user. For days he was not in office, the cells will remain as it is (white background cells)

 

[L. Howard]

For days he was not in office, the cells will remain as it is (white background cells)

Hi y3kesprit,

I don't understand the white background cells. What sheet, what cells?

Here is where I am on your new workbook format.

You will need to test fly it to see if it is heading in the right direction.

The columns K & L are to count the In's & Outs allowing two of each. Those columns can be off screen if needed.

Howard


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

 

[y3kesprit]

Hi y3kesprit,

I don't understand the white background cells. What sheet, what cells?

Here is where I am on your new workbook format.

You will need to test fly it to see if it is heading in the right direction.

The columns K & L are to count the In's & Outs allowing two of each. Those columns can be off screen if needed.

Howard


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

Okay Howard! Really thanks for your help thus far.

I will take a look and get back to you.

What I meant for that part applies to the master list.

On the master list, I am able to see the overview of seven work days. So for example, when an employee checks in on Monday, the cells from Monday onwards on the master list will turn green. Subsequently when he checks out on Friday, Monday to Friday will become red, while Saturday and Sunday will be white cells (since he isn't in office during the weekends).

 

[y3kesprit]

Hi y3kesprit,

I don't understand the white background cells. What sheet, what cells?

Here is where I am on your new workbook format.

You will need to test fly it to see if it is heading in the right direction.

The columns K & L are to count the In's & Outs allowing two of each. Those columns can be off screen if needed.

Howard


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

Hi Howard, the latest one you posted is almost there!

There are only one final issue left.

1) the conditional formatting. From what I see, the cells will turn green from the day he checked in. However when he checked out, the entire row is red. Is it possible to make the days he was in office to be red, instead of the whole row. So if he comes in from Monday to Friday, Mon to Fri will be red. While sat and sun will stay default.