Employees attendance tracking template

Y

y3kesprit

Board Regular
Joined
Mar 23, 2010
Messages
133
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:

qzqhat.png


Spreadsheet 2: work days

2cjxvr.png


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!
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
Hi y3kesprit,

How about posting a link to a workbook that is as close to what you think the finished book will be, format and layout of data. I use Drop Box but there are others. Like the snap shot you posted, but the workbook it's self. (Don't use anything I have to log into or subscribe to.)

Include all the bells and whistles you want in detail in plain terms as much as you can. I want this cell to be xxx and this one to be yyy etc.

I will give it a look, who knows, we might get lucky.

Regards,
Howard
 
Upvote 0
L. Howard said:
Hi y3kesprit,

How about posting a link to a workbook that is as close to what you think the finished book will be, format and layout of data. I use Drop Box but there are others. Like the snap shot you posted, but the workbook it's self. (Don't use anything I have to log into or subscribe to.)

Include all the bells and whistles you want in detail in plain terms as much as you can. I want this cell to be xxx and this one to be yyy etc.

I will give it a look, who knows, we might get lucky.

Regards,
Howard
Click to expand...


Thanks for that advise!

Here is the dropbox link for the workbook!

https://www.dropbox.com/s/bkobapxxwj0y9so/tem.xlsx
 
Upvote 0
Hi y3kesprit,

Have a look here, as a place to start.

I modified the workbook a little, added several more employees and named them with easy to produce and numeric names.

There is a button for each days sheet. Click the button and follow the prompt on the InputBox. You will enter an employee ID number followed by a comma and either an I or an O. No space.

"I" is for signing in and "O" is for signing out. Make the entry and click OK and note the time in the first empty Time In or Time Out.

You have columns for 5 ins and outs. However I am not sure how you want to manipulate the times.

On the daily sheets with the provisions for five in/outs you only have a single provision for the master list sheet.

As of now the Master list sheet in not in play. Not sure how that is going to work.

(There is a typo in the InputBox prompt instructions, easy enough to fix latter.)

Howard


https://www.dropbox.com/s/4fd68b0xyvl3pjw/Employee Attendance Tracker Drop Box.xlsm
 
Last edited:
Upvote 0
Hi Howard,

I tested the spreadsheet. The idea you came up with is workable! However I have one question.

Is it possible for the employee to scan their ID card's bar code at the daily prompts instead of typing in manually? As the ID can be quite long, I am trying to minimize the chances of human error.

As for the Master list sheet, my idea is that it is like an overview. When I can tell at one glance, the times and dates that an employee has checked in and out for the week. As employees might sometimes be absent from work due to reasons like calling in sick, attending conferences or training lessons, I want to be perhaps have a drop down menu to indicate the reasons of absence. Also I would like to have some kind of conditional formatting, where employees who are in office would have an entire row of green-filled cells while those who are absent will have red-filled cells. I can do the conditional formatting myself but I just shared here so that you have an idea of what I am trying to achieve.

If the above is possible to accomplish, is it possible for Excel to auto sort out the dates and times according to the headers of Mon-Fri on the 'master list'? For example, employee A checked in and the entry displays 27 05 14, 0800. Is Excel able to automatically put this entry for employee A under the Tuesday column?

Thanks for your help Howard! Really appreciate it!
 
Upvote 0
y3kesprit said:
Is it possible for the employee to scan their ID card's bar code at the daily prompts instead of typing in manually? As the ID can be quite long, I am trying to minimize the chances of human error.
Click to expand...

Lets just take the scan issue for now.

I do believe you can use a scan method. I don't have a scanner but if a scan into a cell is the same as typing in an entry and hitting ENTER then I think we are okay.

Do you know if that is the case.

Maybe you can test your scanner by putting this change event macro in a clean sheet and then scan something into a cell, see if you get the message box alert.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  MsgBox "A change was made on the sheet"
End Sub

Also, how do you determine what cell the scan goes into? If you get the message when scanning then I can proceed adapting the code to accept a scan, and we can decide what cell to use. Perhaps use one cell for scanning in and another for scanning out.

Let me know how all that works out with the scanner. If the test is positive I can simulate scans with manual entries to develop and test the code.

Howard
 
Upvote 0
L. Howard said:
Lets just take the scan issue for now.

I do believe you can use a scan method. I don't have a scanner but if a scan into a cell is the same as typing in an entry and hitting ENTER then I think we are okay.

Do you know if that is the case.

Maybe you can test your scanner by putting this change event macro in a clean sheet and then scan something into a cell, see if you get the message box alert.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
  MsgBox "A change was made on the sheet"
End Sub

Also, how do you determine what cell the scan goes into? If you get the message when scanning then I can proceed adapting the code to accept a scan, and we can decide what cell to use. Perhaps use one cell for scanning in and another for scanning out.

Let me know how all that works out with the scanner. If the test is positive I can simulate scans with manual entries to develop and test the code.

Howard
Click to expand...

Hi Howard, thanks for your quick reply!

I am not in the office today, so I can't test the scanner with the spread sheet. I will do it tomorrow and update this thread again.

The code that you provided, is it supposed to enable scanning instead of typing in the prompt pop up box?
 
Upvote 0
Hey y3kesprit i have a question that as nothing to do with you spreadsheet.... Can you tell me how did you put the snap shot of your spreadsheet in your post?
 
Upvote 0
y3kesprit, i'm not shure, but, in my opinion i think that you have to create a code bar associated to each of your employee, so the scanner can read it and relate that code bar to the whright employee.....Like we have to do with products in a products database where we have to assign a code to a particular product, i might have to do the same in your case....
 
Upvote 0
Caribeiro77 said:
y3kesprit, i'm not shure, but, in my opinion i think that you have to create a code bar associated to each of your employee, so the scanner can read it and relate that code bar to the whright employee.....Like we have to do with products in a products database where we have to assign a code to a particular product, i might have to do the same in your case....
Click to expand...

Hi Caribeiro77.

Yes you are right... The hardware is in place. Our employees all have a card with a bar code tied to their employee ID. Thanks for your reminder anyway!
 
Upvote 0
You must log in or register to reply here.

Similar threads

O
  • Question
Count only one occurrence of all criteria being met
Replies
8
Views
400
KRice
K
S
  • Solved
Help Required With Barcode Being Read Into A Userform...
Replies
3
Views
803
NoSparks
N
S
  • Question
Automatic accrual calculations by day with accrual rate?
Replies
0
Views
217
Sunshine8790
S
B
  • Question
VBA to show and change list of names depending on selection
Replies
7
Views
452
mumps
mumps
R
  • Question
Excel Table that automatically links data to second sheet and updates automatically when rows are added
Replies
0
Views
364
rosiez7
R

Forum statistics

Threads
1,214,522
Messages
6,120,019
Members
448,938
Latest member
Aaliya13

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top