Hours between two times in Excel and Google Sheets: night shifts, breaks, overtime

A time in a spreadsheet is a fraction of a day: 09:00 is 0.375, 17:30 is 0.729. Once you hold on to that, hours worked is subtraction, night shifts are one MOD, and overtime is one MAX. This is the timesheet arithmetic we use, written so it opens the same in Excel 2016, Google Sheets and Numbers.

Contents

The ×24 rule

in B2, out C2 — hours as a decimal

=(C2 - B2) * 24            09:00 → 17:30 = 8.5

C2 - B2 is a fraction of a day; multiplying by 24 turns it into hours you can add, average and multiply by a rate. Format the result as a plain number (0.00), not as a time — a time format would show 8.5 hours as 12:00, because it reads 8.5 as eight and a half days.

If you would rather see 8:30, keep the raw difference (=C2-B2) and format it [h]:mm. The square brackets let the hours run past 24 when you total a week; without them, 45 hours shows as 21:00.

A time is the fraction; the whole number is the date. A timesheet needs both halves, and the other one — days between two dates, month edges, working days — is date formulas.

Shifts past midnight

22:00 → 06:00 is 0.25 − 0.917 = −0.667, and Excel shows a negative time as ########. Wrap the difference in MOD:

works for day shifts and night shifts alike

=MOD(C2 - B2, 1) * 24      22:00 → 06:00 = 8   ·   09:00 → 17:30 = 8.5

MOD(x, 1) adds a day to a negative fraction and leaves a positive one alone, so the same formula serves both. This is the form our timesheet uses in every row; there’s no need for a separate night-shift column.

Breaks

Type the break in hours (0.5) or as a time (0:30) — just be consistent, because the subtraction differs:

break as hours in D2, or as a time

=MOD(C2 - B2, 1) * 24 - D2          D2 = 0.5
=(MOD(C2 - B2, 1) - D2) * 24        D2 = 0:30

A break typed as 30 (minutes) needs /60. Pick one unit for the column and say it in the header — “Break (h)” — so nobody types 30 into a column of 0.5s.

Overtime

daily: over 8 · weekly: over 40

=MAX(0, E2 - 8)                                  daily, E2 = hours worked
=MAX(0, SUMIFS($E$2:$E$400, $A$2:$A$400, ">="&G2, $A$2:$A$400, "<"&G2+7) - 40)   weekly, G2 = Monday

MAX(0, …) is the whole overtime rule: nothing below the threshold, the excess above it. For weekly overtime, sum the week’s hours with SUMIFS on the date column between the Monday and the next Monday, then apply the same MAX. Regular hours are the total minus overtime, so pay is regular × rate + overtime × rate × 1.5.

Rounding to the quarter hour

MROUND exists in Excel 2016, Sheets and Numbers

=MROUND(E2, 0.25)          8.4 → 8.5 · 8.1 → 8.0
=CEILING(E2, 0.25)         always up · =FLOOR(E2, 0.25) always down

Round the hours value, not the time. Rounding a time to 15 minutes is MROUND(B2, "0:15") in Excel and works in Sheets too, but it rounds the clock-in, which is a different policy from rounding the total.

Entering times so the formulas see times

Type 17:30 or 5:30 PM. 17.30 is a number (seventeen point three) and 1730 is a number, and both will make the subtraction nonsense without an error.
A pasted "09:00" with a leading apostrophe or from a text export is text; =VALUE(B2) or =TIMEVALUE(B2) turns it back into a time.
Don’t put the date and the time in one cell for a timesheet unless you have to. Separate date and in/out columns keep MOD simple and let SUMIFS group by day and week.
TIME(9, 0, 0) builds a time from numbers if you need one in a formula.

Totals

hours this week, this month, per person

=SUMIFS($E$2:$E$400, $A$2:$A$400, ">="&$H$2, $A$2:$A$400, "<="&$H$2+6)
=SUMIFS($E$2:$E$400, $A$2:$A$400, ">="&DATE(2026,9,1), $A$2:$A$400, "<="&DATE(2026,9,30))
=SUMIFS($E$2:$E$400, $B$2:$B$400, "Ana")

Because the hours column is a plain decimal, SUMIFS needs no conversion and the total formats as a number. That’s the reason to do the ×24 in the row rather than at the end.