In this tutorial, you will learn how to make a timesheet in Google Sheets.
Whether you’re working as a freelancer or managing a small business, having an affordable and flexible option for tracking time is important to ensure timely payment and proper records. This tutorial will show you how to set up a basic timesheet template in Google Sheets that can be copied and used repeatedly for all your employees or clients
Here’s how to set up a timesheet template that can be used again and again
To properly log time, you’ll need to track Date, Start Time, End Time and Breaks for each day worked, then calculate the Work Hours. Add these column headers to the spreadsheet
Next, we’ll apply the appropriate number 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
Format the Start and End Times columns as Time using the same method
Format the Breaks and Work Hours columns as Duration
Add the timesheet start date under the Date header, then in the cell below type “=A2 + 1” to put the next date in the cell below
Drag the formula down to cover as many dates as you want. For example, if your company pay period is a month, you might want to drag down the dates for the entire month.
In the Work Hours column in row 2, type the following formula: “=C2-B2-D2” and drag down for all dates. This formula will calculate the total number of hours worked and subtract any breaks entered in the breaks column
To the right of the Work Hours column, add another header for Total Hours and input this formula to sum all hours in the period: “=SUM(E:E)”
If you need to convert the total to decimal time, the formula to do so is “=G2*24”
The timesheet is now ready to use.
Using the Timesheet Template
Once the template is set up, it’s time to use it
Start by making a copy of the template spreadsheet for each employee by opening the file menu and choosing Make a Copy
To update the timesheet date range, simply update the start date stored in cell A2 and the formulas will update all following dates
Add new pay periods to the spreadsheet by clicking on down arrow icon on the initial pay period tab and selecting Duplicate from the menu
The template copy is now ready to use. Enter start and end dates and the duration of breaks into the appropriate columns. The daily totals and period total will calculate
Example Spreadsheet: Make a copy of the example spreadsheet
In this tutorial, I covered how to make a timesheet in Google Sheets. Want more? Check out all the Google Sheets Tutorials.