How to build a timesheet using macros

Tracking work hours is essential for generating accurate payslips, managing the payroll process, and improving overall productivity by understanding where time is spent.

With a spreadsheet-based template, you have complete control over your timesheet. You can decide exactly what information to track and how data is entered, but most importantly, you can define how pay calculations work, including overtime rules and compensation rates.

In this guide, you’ll learn how to build a fully functional timesheet in your spreadsheet using VBA macros to automatically record start and stop times. We'll use Zoho Sheet—a cloud-based spreadsheet application from Zoho's Office Suite. It's also available on iOS and Android.

Follow the steps below to build your timesheet and automate time tracking using macros.

Step 1:  Create your spreadsheet

Open a blank spreadsheet and name it "Timesheet".

Step 2:  Set up the sheet to enter employee timesheet details

Start by adding a place to enter the employee's name, ID number, and any other necessary details, like department or worker classification. Clearly label each data field and indicate which cell the information should be placed in.

Once the employee details are added to the left, create a table with the following column headers where your daily timesheet can be calculated.

• Date: Each row will record working hours for a specific date
• Start time: The time when the employee starts work that day
• Stop time: The time when the employee ends work that day
• Regular hours: Displays standard working hours for the day.
  Eg. 08:00
• Overtime hours: Displays the calculated hours worked beyond the regular time
• Total hours: Displays the total number of hours worked for the day

Step 3: Create macros to track start and stop times

To create a macro:

1. Go to Tools > VBA Macros > Create Macro
2. Enter a name and a description for the macro
3. Click Create
    

In the VBA editor, use the code snippet below to create macros for your start time, stop time and lap.

Note: You can use Lap to stop tracking the current day's time and automatically begin tracking for the next day.

Sub startTime()
   Cells(Rows.Count, 5).End(xlUp).Offset(1, 0).Select
   Selection.Value =  Time
   Selection.NumberFormat = "hh:mm:ss a"
   
End Sub
 
Sub lapTime()
	Dim lastRow As Long
    lastRow = Cells(Rows.Count, "E").End(xlUp).Row
    Cells(lastRow, 6).Value = Time
    Cells(lastRow, 6).NumberFormat = "hh:mm:ss a"
    'Cells (lastRow, 7).Formula = "=F" & lastRow & "-E" & lastRow
    Cells(lastRow+1, 5).Value =  Time
    Cells(lastRow+1, 5).NumberFormat = "hh:mm:ss a"
End 
 
Sub endTime()
	Dim lastRow As Long
    lastRow = Cells(Rows.Count, "E").End(xlUp).Row
   	Cells(lastRow, 6).Value =  Time
    Cells(lastRow, 6).NumberFormat = "hh:mm:ss a"
    'Cells(lastRow, 7).Formula = "=F" & lastRow & "-E" & lastRow
End Sub

 Step 4: Assign macros to buttons 

1. Go to Insert > Button.
2. Enter the button label and resize as needed.
3. Right click on the button and select Assign Existing > VBA Macros from the contextual menu.
4. Select startTime() and click Assign.

Repeat the steps to assign buttons to stopTime and lapTime.

 Step 5: Calculate pay within the timesheet  

After allocating rows for each working day in the weekly timesheet, add the following summary fields:

Total hours: Displays the total number of hours worked during the week across regular and overtime hours.

Hourly rate: Specifies the hourly rate for regular and overtime hours.
Eg. $80.00

To calculate the total hours worked and pay calculated within the timesheet table, use the following functions:

Daily hour calculations:

To calculate total hours worked in a day: =F4-E4
Calculate the duration between Stop time and Start time for the day.

To calculate overtime hours worked in a day: =I4-G4
Calculate overtime hours by subtracting regular hours from total hours worked.

Weekly hour calculations:

To calculate total regular hours worked in a week: =SUM(G4:G10)

To calculate overtime hours worked in a week: =SUM(H4:H10)

To calculate total hours worked in a week: =SUM(I4:I10)

Pay calculations:

To calculate total pay for regular hours: =G11*24*G12
Multiply the total regular hours worked in the week by the regular hourly rate.  

To calculate total pay for overtime hours: =H11*24*H12
Multiply the total overtime hours worked in the week by the overtime hourly rate.

Note: We multiply the number of hours by 24 because spreadsheets store time as a fraction of a full day, so this converts the value into actual hours before multiplying it with the hourly rate.

To calculate total pay for the week: =G13+H13
Calculate the total weekly pay by combining regular pay and overtime pay.

Here is the Timesheet I built for you to try it out.