How to Calculate Time Worked in Google Sheets
Google Sheets can calculate work durations when start and end times are stored as time values. For reliable results, use a duration format, handle overnight shifts explicitly, and convert the final duration to decimal hours only when needed.
Practical timekeeping guide · ShiftHours
Set up the columns
Use columns for start time, end time, unpaid break minutes, net duration, and decimal hours. Enter times consistently, such as 9:00 AM and 5:30 PM, and make sure the cells are recognized as times.
A formula may look correct but return a confusing display if the cell format is set to a time of day rather than a duration.
Calculate a shift duration
For time-only entries in A2 and B2, =MOD(B2-A2,1) handles an end time that falls after midnight. If C2 contains unpaid break minutes, subtract C2/1440 from that duration.
If your sheet stores full date-and-time values, use the difference between the two date-time cells instead of the time-only formula.
Convert to decimal hours
Multiply a duration stored as a fraction of a day by 24. For example, a duration of 7 hours 45 minutes is 7.75 decimal hours. Format the output as a number with the precision you need.
If a value is used in further calculations, avoid rounding intermediate results unless required.
Validate your sheet
Test a daytime shift, an overnight shift, a zero-break case, and a case with an unpaid lunch. Compare the totals with the ShiftHours work hours calculator and check formulas before relying on the sheet for pay records.
Use the free ShiftHours work hours calculator to calculate shift length, subtract unpaid breaks, and view decimal hours. For weekly totals, try the weekly calculator.
Spreadsheet check: use durations carefully
For a same-day shift, a spreadsheet can calculate elapsed time by subtracting the start cell from the end cell. If the result is a duration, format the result cell as a duration such as [h]:mm so totals over 24 hours display correctly. Overnight shifts need a formula or date values that account for the next day.
Related resources
Open the work hours calculator · Browse all ShiftHours articles · Decimal hours chart