A shared Google Sheets timesheet works best when each row represents one work interval and the summary groups by both employee and workweek. The layout below uses full timestamps, explicit unpaid-break minutes and a validation column. It keeps incomplete entries visible instead of turning them into plausible-looking totals.
Create the input layout
Name the first tab Entries and use these columns. Employee IDs can be non-sensitive internal identifiers; a timesheet does not need personal medical notes or details of why someone was absent.
| Column | Heading | Type |
|---|---|---|
| A | Employee | Consistent ID, such as E01 |
| B | Start | Date and time |
| C | End | Date and time |
| D | Unpaid break minutes | Number, including 0 |
| E | Check | Formula |
| F | Hours | Formula, decimal hours |
| G | Week starting | Formula, date |
Choose the spreadsheet locale and time zone before entering dates. Format B:C as date/time, D and F as numbers, and G as a date. The formulas below use English function names and comma separators; some locales require semicolons. Enter timestamps as recognized date values, not text that only looks like a date.
Validate and calculate each row
Place the following formula in E2. A blank employee ID, missing timestamp, negative break or reversed interval produces CHECK. An entirely unused row remains blank.
=IF(COUNTA(A2:D2)=0,"",IF(OR(A2="",COUNT(B2:D2)<3),"CHECK",IF(OR(C2<B2,D2<0,D2>(C2-B2)*1440),"CHECK","OK")))Use these formulas in F2 and G2, then copy all three down through row 1000.
F2: =IF(E2="OK",(C2-B2)*24-D2/60,"")
G2: =IF(E2="OK",INT(B2)-WEEKDAY(INT(B2),2)+1,"")The result in F is decimal hours, so 7.5 means seven hours and thirty minutes. Do not format it as a clock time. G uses the Monday of the start date as a reporting key. Split shifts at a workweek boundary if hours need to be assigned to different weeks; the start-date key cannot do that split for you.
Summarize by employee and week
Create a tab named Summary. Put Employee in A1, Week starting in B1 and Hours in C1. Enter E01 in A2 and the Monday date in B2. Use two criteria so another employee's hours are not included in the total.
=SUMIFS(Entries!$F$2:$F$1000,Entries!$A$2:$A$1000,A2,Entries!$G$2:$G$1000,B2)Add an overall review count outside the source table:
=COUNTIF(Entries!E2:E1000,"CHECK")A zero-hour summary can mean no work, the wrong employee ID, a mismatched week date or invalid source entries. Check those possibilities before approving it. In particular, use a real date in Summary B2, not a label such as “week of Monday.”
Test with a small dataset
For E01, enter October 19, 2026 from 09:00 to 17:30 with 30 break minutes, then October 20 at 22:00 through October 21 at 06:00 with 30 break minutes. The results should be 8 and 7.5 hours, totaling 15.5 for the week beginning October 19. Add a separate E02 row of four hours in that week: E01's summary must stay at 15.5.
Next, remove the end timestamp from an E01 row. CHECK should appear and that row should contribute no hours until corrected. A one-hour interval with a 90-minute break must also be flagged. A valid zero-hour entry remains numerically zero, which is different from an incomplete row.
Protect the shared workflow
Protect formula columns, give editing access only to the people who need it, and keep summary rows outside the input range. Extend all ranges together if entries exceed row 1000. This copyable layout does not include a downloadable file or an automatic payroll integration.
Full timestamps handle midnight and multi-day spans, but these subtraction formulas do not resolve daylight-saving changes or mixed time zones. Record an explicit elapsed-time correction when needed and review it before payroll. Whether breaks or leave should be paid remains a separate policy question.
Official formula references: Google Sheets SUMIFS and SUMIF.
Related calculators and guides
- Work Hours Calculator
- Timesheet Control Totals: Reconcile Before Import
- Check a Decimal-Time CSV Before a Payroll Import
Explore more in Hours & Pay Calculators and Guides.