Course / Time & Schedules

Timesheets and hours worked

Subtract start from end, handle shifts that run past midnight with MOD, convert to decimal hours, and total a week without the clock wrapping round. Intermediate · 5 min · End − start, MOD for overnight shifts, × 24, [h]:mm

A shift's length is end minus start. Times are fractions of a day, so the answer is a fraction too: an 8½-hour shift is 0.354 of a day, which a duration format shows as 8:30.

Two things catch people out:

• Shifts that end after midnight, where the end time is smaller than the start time
• Totals of more than 24 hours, which an ordinary time format wraps round like a clock face

The sheet is Noor's week at an all-night diner, with two late shifts.

Steps

1. Start simple. In D2:D6, subtract each start time from its end time.

2. Fix the late shifts. Change D2 to =MOD(C2-B2, 1), then fill it down to D6 again.

3. Payroll wants decimal hours: 8.5, not 8:30. A day has 24 hours, so in E2:E6, multiply each shift by 24.

4. Total the week. In D8, SUM the shifts in column D. Then in E8, SUM the decimal hours in column E.

5. D9 uses the [h]:mm format. The square brackets let the hours keep counting past 24. Show the week total there by pointing at D8.

Remember

• A shift's length is end − start, shown with an h:mm format.
• =MOD(end-start, 1) handles shifts that run past midnight.
• Multiply a duration by 24 to get decimal hours for pay.
• Format totals as [h]:mm so they don't wrap round at 24 hours.

Previous: How Excel stores times · Next: Pay and overtime