Many small offices lose a lot of money as they do not have a proper tool to track their employee‘s timesheet. With Excel, we can build an automated timesheet tracker. Employees have to sign in and sign out to capture hours worked for a day.
Download Now
If you want to start to use the software as soon as possible, then you can:
Otherwise, if you want to DIY, you can read the contents below.
Let’s Prepare the GUI
We just need a single sheet in the workbook. Rename the single sheet as “Database”. Create a table on the sheet to log sign in and sign out time.
Let’s make it functional
Copy the macro to a new module in your workbook. Attach it to the button that you have created on the sheet “Database”
Sub Click_Here()
Dim v_lr As Long
Sheets("Database").Unprotect
If Sheets("Database").Buttons("Login_Button").Caption = "Sign In" Then
v_lr = 0
For r = 11 To 41
If CStr(Format(Range("B" & r).Value, "mm/dd/yyyy")) = CStr(Format(Now, "mm/dd/yyyy")) Then
v_lr = r
Exit For
End If
Next r
If (v_lr = 0) Then
MsgBox ("Today is not in the timesheet")
Else
Sheets("Database").Range("D" & v_lr).Value = Format(Now, "[$-F400]h:mm:ss AM/PM")
Sheets("Database").Buttons("Login_Button").Caption = "Sign Out"
End If
Else
v_lr = 0
For r = 11 To 41
If CStr(Format(Range("B" & r).Value, "mm/dd/yyyy")) = CStr(Format(Now, "mm/dd/yyyy")) Then
v_lr = r
Exit For
End If
Next r
If (v_lr = 0) Then
MsgBox ("Today is not in the timesheet")
Else
Sheets("Database").Range("E" & v_lr).Value = Format(Now, "[$-F400]h:mm:ss AM/PM")
Sheets("Database").Buttons("Login_Button").Caption = "Sign In"
Range("B7").Value = Range("G7").Value / Int(Split(Range("G5").Text, ":")(0))
Range("E7").Value = Range("B7").Value * Int(Split(Range("E5").Text, ":")(0))
End If
End If
Sheets("Database").Protect
End Sub
How does it work?
When the macro runs, it identifies the last used row in the table. It then reads the current time using Now function. With this, the login time of the employee is captured and added to the table. If the same button is pressed, the macro logs the current time as log out time. Please do note, you have to update fields above the table with appropriate values to get total hours worked and the invoice amount. You can either use formula or macro to populate total hours and total cost.
Tweak it
This macro captures the time entries for a single employee. Add a new sheet, upload all employees’ info into the sheet. Create a drop down using unique names of the employees and then allow employees to pick their name from the drop down and log their time. A small user form can be used to create the login form with which each employee can enter their login ID and the password. Create a sheet and add employees’ and their credential. When the user submits their ID and password through the form, the entry can be checked against the database. If the id and password do not match with the credential on the database, show an error message so that the user can retry.
Interactive dashboard
You can also create an interactive dashboard that shows employees who have not filled their timesheet or employees who were on leave for multiple days or employees who have done over time. Make sure that the code you add to tweak the current script is compatible with your Excel’s version. An incompatible code has higher chances of corrupting xlsm files and you need to repair Excel file accordingly.
Author Introduction:
Nick Vipond is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including damaged word and outlook recovery software products. For more information visit www.datanumen.com.
Leave a Reply