How to Make a Timesheet in Google Sheets

Build a weekly timesheet in Google Sheets from a blank sheet: layout, time formats, the hours formula, totals, overtime and sharing.

Updated 2026-10-20

Ten minutes from a blank sheet to a working timesheet. Or skip to the end and import a finished template.

1. Headers

Row 1: Day, Date, Time in, Time out, Break (min), Hours. Freeze it with View > Freeze > 1 row.

2. Days and dates

A2:A8 Monday to Sunday. B2 the week’s Monday date; B3 =B2+1 filled down.

3. Time format

Select C2:D8, Format > Number > Time. Now 9am, 9:00, 17:30 and 5:30 pm are all understood.

4. Hours formula

F2:

=IF(OR(C2="",D2=""),0,(D2-C2+(D2<C2))*24-E2/60)

Fill down to F8. Format F2:F8 as Number with 2 decimals. The (D2<C2) adds a day for overnight shifts; E2/60 converts break minutes to hours.

5. Totals and overtime

6. Pay

Rate in B13. F13: =ROUND(F10*B13+F11*B13*1.5,2). Format as currency.

7. Make it hard to break

Select the formula cells and Data > Protect sheets and ranges so only the punch cells are editable. Add Data > Data validation on C2:D8 requiring a valid time.

8. One tab per week

Right-click the tab > Duplicate for next week and change B2. For a year of weeks, add a Summary tab with ='Week 1'!F13 style references and a SUM.

9. Sharing

Share with the employee as Editor on their own copy, or collect via a Google Form that feeds a sheet and compute hours in a second tab with ARRAYFORMULA.

10. Or import a template

All of our Excel templates import into Sheets: File > Import > Upload > Insert new sheet. The weekly, with-lunch, overtime and multi-employee versions are the most used.

When the rules get complicated (California daily overtime, rounding, different rates) the Timesheet Calculator is faster and exports back to Sheets as an .xlsx.

Daily overtime for California and other daily-rule states

Add columns G (Regular) and H (Daily OT): =MIN(F2,8) and =MAX(0,F2-8). Weekly overtime then applies only to the regular column: =MAX(0,SUM(G2:G8)-40). Total overtime is =SUM(H2:H8) plus that. Colorado uses 12 in place of 8; California also needs a Double time column =MAX(0,F2-12) and caps daily OT at =MAX(0,MIN(F2,12)-8).

Mobile entry

The Google Sheets app accepts the same time formats, and a sheet shared with an employee can be filled from a phone at the job site. Lock everything except the punch cells first so a mis-tap cannot delete a formula.

Automatic week dates

In B2 type =TODAY()-WEEKDAY(TODAY(),2)+1 for the current Monday, then =B2+1 downward. For a fixed week, type the Monday date and the rest follow. Format B2:B8 as Date.

Timestamps with a button

A checkbox in column G (Insert > Checkbox) combined with =IF(G2,NOW(),"") looks like a clock-in button but NOW() recalculates on every edit, so the time will drift. For real timestamps use a Google Form (which records submission time) or an Apps Script onEdit trigger that writes the current time as a value.

Frequently asked

Why does my hours column show 0.33 instead of 8? The *24 is missing. A time difference is a fraction of a day until multiplied by 24.

Why do I get −16 for a night shift? The (D2<C2) term is missing, so Sheets does not add the day when the out time is before the in time.

Can I sum H:MM durations? Yes, if the cells are formatted as Duration. SUM works, and the total can exceed 24 hours. For pay calculations convert to decimal with *24.

How do I round to the nearest 15 minutes? =MROUND(C2, "0:15") on each punch cell. Do not round the daily total.

Does importing our Excel template change anything? No. Formulas, formats and widths import unchanged; only Excel-specific features such as data validation messages may need re-adding.

Can I email the finished sheet as a PDF? File > Email > Email this file, choose PDF. Set the print area to the timesheet range first under File > Print so the PDF is one page.