If you’re a freelancer or a small business owner, it’s crucial to have an affordable and flexible option for tracking time to ensure timely payment and accurate records. In this article, I will show you how to create a basic timesheet template using Google Sheets that can be copied and used repeatedly for all your employees or clients.
Setting up the Timesheet
Here’s how you can create a reusable timesheet template:
Step 1
To properly record time, you will need to track the date, start time, end time, and breaks for each workday, and then calculate the total working hours. Add these column headers to the spreadsheet.
Step 2
Next, apply the appropriate formatting to these columns. To format the date column as a date, select the entire column by clicking on the column letter, then open the Format menu, hover over Number, and choose Date.
Step 3
Format the Start Time and End Time columns as Time using the same method.
Step 4
Format the Breaks and Total Hours columns as Duration.
Step 5
Add the starting date of the timesheet under the Date header, then in the next cell, type “=A2 + 1” to populate the next date in the cell below.
Step 6
Drag the formula down to cover as many dates as you need. For example, if your company’s pay period is one month, you may want to extend the dates for the entire month.
Step 7
In the Total Hours column, at row 2, type the following formula: “=C2-B2-D2” and drag it down for all the dates. This formula will calculate the total number of hours worked and subtract the breaks entered in the Breaks column.
Step 8
To the right of the Total Hours column, add another header for the period’s total hours and enter this formula to sum up all the hours in the period: “=SUM(E:E)”
Step 9
If you need to convert the total into decimal hours, use the formula “=G2*24”.
Step 10
The timesheet is now ready to be used.
Using the Timesheet Template
Once the template is set up, it’s time to put it into use:
Step 1
Start by making a copy of the template spreadsheet for each employee by opening the File menu and choosing “Make a copy”.
Step 2
To update the date range of the timesheet, simply update the start date stored in cell A2, and the formulas will update all the following dates.
Step 3
Add new pay periods to the spreadsheet by clicking the downward arrow on the initial pay period tab and selecting “Duplicate” from the menu.
Step 4
The copied template is now ready to be used. Enter the start and end dates, as well as the duration of breaks in the appropriate columns. The daily totals and the period’s total will calculate automatically.
Conclusion
In this article, I’ve explained how to create a timesheet using Google Sheets. Want more? Check out all the Google Sheets tutorials on Crawlan.com.
