Google Sheets Timesheet Formulas That Actually Work

Hours worked, breaks, overnight shifts, overtime and pay formulas for Google Sheets, plus the differences from Excel.

Updated 2026-10-20

Google Sheets handles times exactly like Excel: a time is a fraction of a day, so subtracting two times and multiplying by 24 gives hours. Almost every Excel timesheet formula pastes straight in. This guide covers the formulas and the few Sheets-specific details worth knowing.

Hours worked

Time in B2, time out C2, break minutes D2:

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

The (C2<B2) part adds one day when a shift ends after midnight. The IF keeps blank days at zero instead of showing a large negative number.

Entering times

Sheets recognises 9:00, 9:00 AM, 9am, 17:30 and 5:30 pm. It does not recognise 900 or 1730; those become plain numbers. If your team types four-digit military time, convert it with a helper column:

=TIME(INT(E2/100),MOD(E2,100),0)

Then point the hours formula at the helper column.

Totals and overtime

Overtime is per 7-day work week. On a bi-weekly sheet, total each week on its own and add the two gross pay figures.

Using ARRAYFORMULA

Sheets can fill a whole column with one formula, which keeps new rows consistent:

=ARRAYFORMULA(IF((B2:B="")+(C2:C=""),0,(C2:C-B2:B+(C2:C<B2:B))*24-D2:D/60))

Put this in F2 and leave F3 onward empty. Note that OR does not work element-wise inside ARRAYFORMULA, which is why the two tests are added with + instead.

Duration formatting

To display 8.5 as 8:30, divide by 24 and apply Format > Number > Duration. Durations can exceed 24 hours, so weekly totals display correctly. Keep the decimal column too, because payroll systems import decimals.

Rounding to payroll increments

Round to the nearest quarter hour: =MROUND(F2,0.25). Round to the nearest tenth: =ROUND(F2,1). Note that legal time-clock rounding applies to each punch, not the daily total; see the guide on the 7-minute rule.

Sharing with a team

Protect the formula cells (Data > Protect sheets and ranges) so people can only type in the punch cells. Use File > Make a copy for each employee, or one tab per person with a summary tab using ='Alex'!F9 style references.

Importing our templates

All of the Excel templates on this site import cleanly: File > Import > Upload, choose the .xlsx, and select Insert new sheet. Formulas, formats and column widths survive the import.

Collecting punches with a Google Form

A form with Date, Time in, Time out and Break questions writes rows to a linked sheet. Add a second tab that references the responses with ARRAYFORMULA, so each submission computes hours automatically:

=ARRAYFORMULA(IF('Form Responses 1'!B2:B="",,( 'Form Responses 1'!C2:C-'Form Responses 1'!B2:B+('Form Responses 1'!C2:C<'Form Responses 1'!B2:B))*24-'Form Responses 1'!D2:D/60))

Then summarise per employee per week with QUERY:

=QUERY(Hours!A:F, "select A, sum(F) where B >= date '2026-03-02' group by A", 1)

Daily overtime in Sheets

The same approach as Excel: Regular =MIN(F2,8) and Daily OT =MAX(0,F2-8), then weekly overtime on the regular column only: =MAX(0,SUM(G2:G8)-40). For Colorado change the 8 to 12. For California add a Double time column =MAX(0,F2-12) and cap Daily OT at =MAX(0,MIN(F2,12)-8).

Conditional formatting for errors

Highlight impossible entries: select F2:F8, Format > Conditional formatting, custom formula =OR(F2<0,F2>16), red fill. Shifts over 16 hours are almost always a typo such as PM for AM.

Frequently asked

Why does 9:00 − 17:30 give a weird time instead of hours? The result cell is formatted as a time. Set it to Number, or multiply by 24 (which the formulas above already do).

Why does my total show 3:30 for 27.5 hours? The duration format is wrapping at 24 hours. Use Format > Number > Duration, which does not wrap, or keep decimals.

Can I type 1730 instead of 17:30? Not directly; Sheets reads 1730 as a number. Use the TIME helper formula or ask people to include the colon. Our military time card calculator accepts bare four-digit entries and can export the result to Sheets.

How do I stop people editing formulas? Data > Protect sheets and ranges on the formula cells, and share with Editor access only to the punch range. Or collect through a form as above so the sheet itself is never edited by hand.