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]

I realised the green highlighting only works on Monday onwards.

When I check in on Sunday, the entire row will be highlighted green instead of just Sunday onwards.

 

[y3kesprit]

If done right, at the end of the week, only the days that an employee had been in office, will the cells be red. So for example, if he comes in on Monday, leaves on Tuesday, comes in on Thursday and leave on Friday, then Monday, Tuesday, Thursday and Friday will be red. This is on top of the formatting that cells be green whenever he checks in.

 

[L. Howard]

From you post #35

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.

So what is the purpose of having Sat & Sunday on the overview sheet and why do they both have Time In and Time out columns?

What do you want to have happen on Sat and Sun, if anything, and when do you want it to happen?

Howard

 

[y3kesprit]

From you post #35

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.

So what is the purpose of having Sat & Sunday on the overview sheet and why do they both have Time In and Time out columns?

What do you want to have happen on Sat and Sun, if anything, and when do you want it to happen?

Howard

Oh this is because of the shift working hours which results in employees having to work on weekends as well. So sat and sun can be considered as a normal work day for those who are on shift. It is possible for one to report to work on a Saturday and leave on a Sunday.

 

[L. Howard]

Yes, have you not tried it?

I signed A12 in on Sat, green line to end of week. Signed A12 out on Sun, red line across week.

I just noticed there may be a problem with the coding. Looks like signing in on Sunday shows as in on Sat.

I'll have a look at that.

Howard

 

[y3kesprit]

Yes, have you not tried it?

I signed A12 in on Sat, green line to end of week. Signed A12 out on Sun, red line across week.

Howard

The green is good. It's the red. If he signed out on Sunday, only sat and sun should ideally be red. Indicating that he was in office on sat to Sunday this week and has since checked out.

Also when I checked in on Sunday, the entire row is green instead of Sunday onwards being green which is the case for Monday to Friday sheets.

 

[L. Howard]

I think you are talking about the error I just mentioned.

Do this:

On the Sun sheet tab right click and click on View code.

Scroll all the way down to near the bottom (second Sub from the bottom) and find the sub routine I have posted here.

Copy this one and select (highlight) the one in the VB Editor and paste this corrected version over it.

Now go back to the sheet and try it out.

Howard

Sub Green_Scan_In()

Dim Lrowg As Long
Dim cRngIn As Range
Dim coloValg As String
Dim cvg As Range
Dim lcolg As Long, i As Long

coloValg = Sheets("sun").Range("B1")

If coloValg = "Scan In" Then Exit Sub

Lrowg = Sheets("overview").Cells(Rows.Count, "B").End(xlUp).Row

Set cRngIn = Sheets("overview").Range("B6:B" & Lrowg)

For Each cvg In cRngIn
If cvg = coloValg Then
   If cvg.Offset(, 9).Interior.ColorIndex <> 4 Then
      cvg.Offset(, 9).Resize(1, 24).Interior.ColorIndex = 4
   End If
End If
Next

End Sub

 

[y3kesprit]

I think you are talking about the error I just mentioned.Do this:On the Sun sheet tab right click and click on View code.Scroll all the way down to near the bottom (second Sub from the bottom) and find the sub routine I have posted here.Copy this one and select (highlight) the one in the VB Editor and paste this corrected version over it.Now go back to the sheet and try it out.Howard

Sub Green_Scan_In()Dim Lrowg As LongDim cRngIn As RangeDim coloValg As StringDim cvg As RangeDim lcolg As Long, i As LongcoloValg = Sheets("sun").Range("B1")If coloValg = "Scan In" Then Exit SubLrowg = Sheets("overview").Cells(Rows.Count, "B").End(xlUp).RowSet cRngIn = Sheets("overview").Range("B6:B" & Lrowg)For Each cvg In cRngInIf cvg = coloValg Then   If cvg.Offset(, 9).Interior.ColorIndex <> 4 Then      cvg.Offset(, 9).Resize(1, 24).Interior.ColorIndex = 4   End IfEnd IfNextEnd Sub

Hi Howard, That settled the problem on Sunday. Is there any way to solve the red cells based on the number of days an employee was in office? So if he was in on Sunday, and he left on Tuesday, Sunday - Tuesday becomes red when he checks out.

 

[L. Howard]

Is there any way to solve the red cells based on the number of days an employee was in office? So if he was in on Sunday, and he left on Tuesday, Sunday - Tuesday becomes red when he checks out.

Not sure, I'll see what I can do.

Howard

 

[y3kesprit]

Not sure, I'll see what I can do.

Howard

OK thanks Howard. Thanks for helping with this project all the way. Really appreciate it