How to Calculate Work Hours in Google Sheets

All guides Spreadsheets

Quick answer

In Google Sheets, record each endpoint with its date and time, subtract the two values, then subtract unpaid break minutes divided by 1,440.

Open Time Card Calculator

Step by step

  1. Put start and end date/time values in A and B, and unpaid break minutes in C. Use Format → Number → Date time on A and B. Set the next date on an overnight end.
  2. In D2 enter =IF(OR(A2="",B2=""),"",IF(OR(COUNT(A2:B2)<2,AND(C2<>"",NOT(ISNUMBER(C2)))),"Check inputs",IF(OR(C2<0,B2-A2<C2/1440),"Check inputs",B2-A2-C2/1440))). Fill down and format column D with Format → Number → Duration.
  3. Resolve all flagged rows before using =SUM(D2:D8) for the week. Apply Duration to the total. For decimal hours, multiply that total by 24 in a Number cell.

Worked examples

Dated overnight shift

Start Nov 9, 2026 9:45 p.m. and end Nov 10, 2026 5:30 a.m. with 30 unpaid minutes. The result is 7:15:00.

Spot an invalid row

If 08:00 to 08:30 is paired with a 45-minute break, the row reads Check inputs. The same message appears for text such as “eight thirty” in the end cell. Correct the entry before totaling.

What to check

Under File → Settings, check the locale if input dates are interpreted differently. Use the date picker or an unambiguous entry supported by the sheet’s locale.

Related guides

Sources

See the calculation methodology or report an error.