Confident Cells

Hours worked in Excel: shifts, breaks and timesheets

20 November 2026 · 2 min read

Hours worked is the end time minus the start time: =C2-B2. Excel stores times as fractions of a day, so multiply by 24 to get decimal hours for pay: 7 hours 45 minutes becomes 7.75.

Shift length

Priya works from 07:00 to 15:45. That's 8 hours and 45 minutes.

=C2-B2

Format the answer cell as a time (h:mm) and it shows 8:45.

Decimal hours for pay

Pay is worked out per hour, so 8 hours 45 minutes needs to be 8.75. A day is 24 hours, and Excel stores 8:45 as 0.3646 of a day, so multiply by 24:

=(C2-B2)*24

Format this cell as a number, not a time. The brackets make Excel subtract first.

Taking off an unpaid break

Priya has a 30-minute unpaid break. 8.75 hours − 30 ÷ 60 = 8.25 paid hours. With the break in minutes in D2:

=(C2-B2)*24-D2/60
ABCDE
1NameStartEndBreak (mins)Paid hours
2Priya7:0015:45308.25
3Hamza12:3017:1504.75
4Jo9:0017:30457.75
E2 holds =(C2-B2)*24-D2/60, copied down.

Shifts that go past midnight

A night shift from 22:00 to 06:00 gives a negative answer, because 06:00 is smaller than 22:00. MOD fixes it by adding a day when the end is earlier than the start:

=MOD(C2-B2,1)*24

22:00 to 06:00 gives 8 hours.

A week's total

Add up the h:mm column with SUM, but format the total as [h]:mm. Plain h:mm starts again at 24 hours, so 41 hours would show as 17:00. In decimal hours, =SUM(D2:D8)*24.

Common mistakes

Taking off minutes as hours

=(C2-B2)*24-D2 takes 30 hours off, not 30 minutes. Divide the minutes by 60.

Strange decimals like 0.364583

That's the time as a fraction of a day. Multiply by 24.

##### in the cell

A negative time, usually a night shift. Use MOD as above.

Try it now

Your turn

Breaks are unpaid. In E2, work out each person's paid hours in decimal hours: the shift length, less the break, which is in minutes in column D. Your formula will be copied down to E6.

ABCDE
1NameStartEndBreak (mins)Paid hours
2Priya7:0015:4530
3Hamza12:3017:150
4Jo9:0017:3045
5Kai8:1516:4530
6Lena6:0014:0020