How to Calculate Time for a Timesheet in Excel

A spreadsheet can calculate timesheet durations from start and end times, but cell formatting matters. Use real time values, account for shifts crossing midnight, and subtract unpaid breaks consistently.

Practical timekeeping guide · ShiftHours

Enter start and end times

Place the start time in cell A2 and the end time in B2. For a same-day shift, a basic duration formula is B2-A2. Format the result as a duration such as [h]:mm if the spreadsheet supports that format.

If the end time is after midnight, use a formula that handles the next day, such as =MOD(B2-A2,1), when only the time-of-day values are stored. Test it with real examples.

Subtract an unpaid break

If break minutes are in C2, convert them to a fraction of a day by dividing by 1440, because a day has 24 × 60 = 1440 minutes. A net duration formula for time-only cells is =MOD(B2-A2,1)-C2/1440.

Check that the break does not exceed the shift and that the output is not negative. Different spreadsheets may use different locale separators.

Display decimal hours

If the net duration is stored as a spreadsheet time fraction, multiply it by 24 to convert it to decimal hours. For example, 7 hours 30 minutes becomes 7.5. Format the result as a number rather than a time of day.

Use [h]:mm for accumulated durations that can exceed 24 hours, or a numeric decimal format for decimal hours.

Test edge cases

Try a normal shift, an overnight shift, zero break, and a shift with a 30-minute break. Confirm results against a manual calculation before using the spreadsheet for payroll. The ShiftHours calculator can serve as a quick cross-check.

Related resources

Open the work hours calculator · Browse all ShiftHours articles · Decimal hours chart