How to Calculate Hours Worked in Excel (timesheet with breaks and overtime)

Times in Excel are fractions of a day, which is why subtracting them and then summing a week goes wrong unless you set it up right. Here is the setup.

Step 1: the columns

Type times as 9:00 and 17:30 (24-hour). Excel recognises them.

Step 2: hours per day, as a decimal

E2:

=(C2-B2)*24 - D2

→ 8. Multiplying by 24 turns a day-fraction into hours. Decimal hours are what payroll wants.

Step 3: weekly total

E9: =SUM(E2:E8). Because these are plain numbers, totals over 24 work fine.

Prefer h:mm display?

Use =C2-B2-D2/24 instead and format the cell as [h]:mm (Ctrl + 1 › Custom). The square brackets stop the total resetting at 24 hours.

Night shift (out is the next day)

=MOD(C2-B2, 1)*24 - D2

22:00 to 06:00 → 8, not −16.

Overtime over 8 hours

F2: =MAX(E2-8, 0). Regular: =MIN(E2, 8).

Pay

Rate in H1. Pay: =MIN(E2,8)*$H$1 + MAX(E2-8,0)*$H$1*1.5.

Check it worked

9:00 to 17:30 with a half-hour break is 8.0. 22:00 to 06:00 is 8.0.

Common mistake

Summing h:mm cells formatted as h:mm — a 45-hour week shows as 21:00. Use [h]:mm or decimals.