Google Form time clock: total weekly hours and overtime

A Google Form makes a free time clock: each employee taps Clock in or Clock out, and every tap lands as a row in a Google Sheet. The hard part is turning those rows into hours and overtime for payroll.

1. Set up the form

Either a Name question as a dropdown with each employee's name (so names are always spelled the same), or turn on collecting email addresses so Google records who submitted.
Clock in or out (multiple choice: Clock in, Clock out).
Optional: Department and Type (Regular, Call-back, Vacation, Sick).

Don't add a "time" question. Use the Timestamp column the form adds by itself: it is the moment Google received the tap, and employees can't type a different time. Then link the responses to a Google Sheet. Each tap becomes a row: Timestamp, Name, Clock in or out.

2. Why simple formulas break

Most templates pair each clock-in with the next row and subtract. That goes wrong when:

someone forgets to clock out, so the next "in" is paired with nothing, or with tomorrow;
a shift crosses midnight, so 10 PM to 6 AM shows as negative hours;
two people's taps are mixed together in time order (they always are);
totals pass 24 hours and display as a time of day instead of hours (format the cell as [h]:mm or multiply by 24).

3. Total the hours with formulas

On a new tab, sort the responses by Name, then Timestamp. For each "Clock out" row, hours worked are the gap since that person's previous "Clock in". In D2, with Timestamp in A, Name in B and In/Out in C:

=IF(AND(C2="Clock out", C1="Clock in", B1=B2), (A2-A1)*24, "")

A blank where you expected hours means a missed punch: fix it at the source.

Then add a Week starting column. For a Sunday start: =INT(A2) - WEEKDAY(INT(A2),1) + 1; for a Monday start: =INT(A2) - WEEKDAY(INT(A2),2) + 1 (format as a date). A shift that crosses midnight into a new week is counted in the week of its clock-out here; under the federal rule the hours belong to the day they were worked, so split those by hand.

Weekly totals per person: a pivot table with rows Name and Week starting, values SUM of D.

4. Overtime

Federal rule: hours over 40 in a workweek are paid at 1.5 times the regular rate, and each workweek stands on its own: you can't average two weeks in a biweekly pay period (U.S. Department of Labor Fact Sheet #23).

Regular: =MIN(40, total)
Overtime: =MAX(0, total-40)

California and some other states also have daily overtime. In California: over 8 hours in a day at 1.5x and over 12 at 2x, plus the seventh-consecutive-day rule (California DLSE overtime FAQ). A weekly formula alone will underpay there.

Unpaid breaks: if people clock out and back in for lunch, the formula already removes the break. If you record break minutes separately, subtract them per shift.

5. Or skip the formulas

WeekClose reads the form's response sheet as it is (Timestamp, Name and "Clock in or out" are matched automatically) and writes a WeekClose Hours tab: regular hours, overtime and double time per employee per workweek, overnight shifts split correctly, and every missed punch listed with its row number. It never edits your responses. Free for up to 5 employees on the federal rule. Want employees to clock in from their phones in two taps? See phone clock-in.

Not legal or payroll advice: check your state's rules. Sources: U.S. Department of Labor Fact Sheet #23, Overtime Pay Requirements of the FLSA; California DLSE Overtime FAQ. Checked November 2026.