Google Sheets Timesheet Template: Setup, Formulas and Tips

To make a timesheet template in Google Sheets, create columns for the date, start time, end time, unpaid break, and total hours, then use a formula to calculate each shift. You can build a reusable sheet from a blank spreadsheet or look in the Sheets template gallery; the steps below show how to set up a simple version that handles overnight shifts and weekly totals.

What a Google Sheets Timesheet Should Track

A timesheet records when work happens and how much time is worked. The right layout depends on whether you are tracking one person, a team, project time, or billable hours. For a straightforward employee or personal log, one row per shift is usually easiest to review and total.

Start with these columns:

Date: the calendar date the shift began.
Start: the time work began.
End: the time the shift ended.
Break (hours): unpaid break time entered as a decimal, such as 0.5 for 30 minutes.
Total hours: the calculated work time after the break is subtracted.
Optional details: employee name, project, task, location, or a note explaining an adjustment.

If several people will use one file, include a name or employee ID column. For project reporting, add a project column and use a separate summary to total hours by project. Keep the core time-entry fields consistent so formulas and filters remain manageable.

Build the Sheet and Format the Entries

In Google Sheets in a web browser, open a blank spreadsheet and type the column headings in row 1. Freeze the heading row with View > Freeze > 1 row so it remains visible while scrolling. If you want to start from a prepared file, open the Sheets template gallery and look for a time or work-hours template. Gallery choices may differ by account, so check that any template you select records breaks and calculates time in the way you need.

Apply suitable formats before entering a full week of data. Select the date column and choose Format > Number > Date. Format the start and end columns with Format > Number > Time. Enter break lengths as decimal hours in the break column, and format the total-hours column as a number, perhaps with two decimal places. A number such as 7.5 means seven and a half hours; a time-of-day format is not the same thing as a decimal hour total.

For clearer entries, select the relevant cells and use Data > Data validation to create dropdowns for a project, work location, or employee. Dropdown choices help avoid variations such as “Client A,” “client a,” and “Client-A” that can split a summary into separate categories. Leave free-text notes in their own column rather than mixing them into the time or date fields.

Calculate Daily Hours With a Formula

Assume the layout is A for Date, B for Start, C for End, D for Break (hours), and E for Total hours, with the first shift on row 2. In E2, enter this formula and copy it down the column:

=IF(OR(A2="",B2="",C2=""),"",MAX(0,MOD(C2-B2,1)*24-D2))

The formula leaves the result blank until a date and both times are entered. It subtracts the break from the shift length and uses MOD to handle an end time after midnight. For example, a shift from 9:00 a.m. to 5:30 p.m. with a 0.5-hour break returns 8. For a shift from 10:00 p.m. to 6:00 a.m. with a 0.5-hour break, it returns 7.5. MAX prevents a negative result if the break entry is accidentally longer than the calculated shift, but you should still correct that input rather than treating zero as a valid work total.

Enter actual spreadsheet times, not text that only looks like a time. If a value does not calculate as expected, select the cell and check its number format, then re-enter the time. In some Google Sheets locales, formula argument separators are semicolons rather than commas; the spreadsheet’s locale determines which separator is accepted. The formula uses standard spreadsheet functions that are also available in Excel, although the menu layout and sharing features differ.

Add Weekly Totals and Optional Overtime Tracking

For a seven-day section where daily totals are in E2:E8, put this formula in the cell where you want the weekly total:

=SUM(E2:E8)

If those entries total 38.5 hours, the formula returns 38.5. Format the result as a number, not as a time of day. For a longer timesheet, make one weekly total row for each date range, or use a summary table that groups entries by week. Avoid including a total cell inside the range it sums, since that can create a circular reference.

You can also calculate hours above a chosen weekly threshold as a tracking aid. Assuming the same weekly hours are in E2:E8 and the threshold is 40 hours, use:

=MAX(0,SUM(E2:E8)-40)

If the week totals 42.5 hours, this returns 2.5; if it totals 38.5, it returns 0. This is only arithmetic against a user-entered threshold, not a determination of legally payable overtime. Overtime rules can depend on location, worker classification, and other details. Confirm the applicable rules with a qualified professional or official source before using a spreadsheet for payroll decisions.

Make the Template Easier to Use

After the calculations work, add modest safeguards and make a clean copy for each reporting period. Consider these improvements:

Protect calculation cells: keep users from accidentally overwriting formulas by using Data > Protect sheets and ranges. Protection controls editing; it is not a substitute for appropriate file-sharing permissions.
Highlight missing entries: use Format > Conditional formatting to flag a row where a date is present but a start or end time is missing. Set a rule that matches the structure of your sheet and test it with a sample row.
Use filters: select the heading row and choose Data > Create a filter. Filters make it easier to view one employee, project, or date range without deleting other records.
Add a notes field: record corrections, approved schedule changes, or unusual shifts with enough context to explain the entry later.
Keep a master copy: maintain a clean template and create a separate copy for each employee or pay period. This reduces the chance that a new period will inherit old entries.

Google Sheets saves changes as you work, but naming files and organizing them in Drive still matters. Use a consistent name that identifies the person or team and period without exposing sensitive information in a public link. Share only with the people who need access, and review the access list when a project or employment relationship ends.

Choose a Layout That Fits the Work

A daily timesheet with one row per shift is simple, but it is not the only useful design. A weekly grid can place days across columns and employees down rows, which is convenient for a small team with predictable schedules. Its weakness is that notes, multiple shifts in one day, and project-level detail can become awkward. A transaction-style table with one row per shift scales better and is easier to filter or summarize.

Before choosing a layout, consider:

How often people work split shifts: use multiple rows per date if someone can start and stop more than once.
Whether hours belong to projects: record a project on each shift or split a shift into separate project entries if exact allocation matters.
Who approves the record: include an approval status or reviewer field if a manager needs to confirm submissions.
How the totals will be used: a personal time log may need only weekly totals, while invoicing or staffing analysis may require project and task detail.

Do not add columns just because they might someday be useful. Every extra field creates more work and another opportunity for inconsistent entries. Start with the information that answers a real reporting or review need, then expand the template if that need becomes clear.

Common Problems and Misconceptions

A frequent mistake is entering a break as 30 rather than 0.5 when the formula expects decimal hours. Another is formatting total hours as a clock time, which can make a duration appear confusing or wrap after 24 hours. Keep break units and result formats explicit in the headings, and test a known shift before distributing the file.

Overnight shifts are another source of errors. A simple subtraction of end minus start can produce a negative duration when the shift crosses midnight. The MOD formula above addresses that case, provided each row represents one continuous shift and the date identifies the shift’s start date. If a person works two separate periods on the same date, use two rows rather than trying to make one row represent both.

A spreadsheet is also not automatically an official timekeeping or payroll system. It can calculate recorded hours, but it does not verify that an employee entered accurate times, determine which rounding rules apply, or establish compliance with workplace requirements. Keep original records and approval practices that match your organization’s needs, and do not treat a formula’s output as proof that the underlying entries are correct.

Google Sheets Compared With Other Options

Google Sheets is useful when people need to enter or review a shared timesheet in a browser. Several collaborators can work in one file, and version history can help identify changes. Sharing is flexible, but it also means access settings need attention. Avoid setting a sensitive timesheet to “Anyone with the link” unless that access is genuinely intended.

Excel can open and calculate a basic timesheet using the same common formulas, but its interface, file-sharing setup, and collaboration experience are not identical to Google Sheets. Dedicated time-tracking services may offer clock-in controls, reminders, approval workflows, audit trails, or integrations that a spreadsheet does not provide by itself. Those tools may cost money or require an account, so compare their features and terms with the actual need rather than assuming a spreadsheet is either always enough or always inadequate.

Frequently Asked Questions

Does Google Sheets have a timesheet template?
Google Sheets has a template gallery, and time-related templates may be available there. The selection can vary, so check the columns and calculations before adopting one. You can also create a reusable template yourself with date, start, end, break, and total-hours columns.

How do I calculate hours worked in Google Sheets?
Enter start and end times as spreadsheet times, record unpaid breaks in a consistent unit, and use a formula that subtracts the start from the end and converts the time fraction to hours. Use MOD in the calculation if a shift can end after midnight.

How do I enter a 30-minute break?
If your break column is labeled in hours and the formula subtracts decimal hours, enter 0.5. If you choose to store breaks as time values instead, revise the formula and label the column clearly so users do not mix the two formats.

Can Google Sheets calculate overnight shifts?
Yes. A formula using MOD can calculate the elapsed time when an end time is earlier on the clock than the start time. Record the date the shift began, and use separate rows for separate work periods.

Can I use a Google Sheets timesheet for payroll?
A sheet can total the entries you provide, but it does not determine whether the records meet payroll, overtime, or recordkeeping requirements. Check the rules that apply to your situation and have the appropriate person review the entries before relying on them for payment.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.