Hours worked in Excel: shifts, breaks and timesheets
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
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Start | End | Break (mins) | Paid hours |
| 2 | Priya | 7:00 | 15:45 | 30 | 8.25 |
| 3 | Hamza | 12:30 | 17:15 | 0 | 4.75 |
| 4 | Jo | 9:00 | 17:30 | 45 | 7.75 |
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
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.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Name | Start | End | Break (mins) | Paid hours |
| 2 | Priya | 7:00 | 15:45 | 30 | |
| 3 | Hamza | 12:30 | 17:15 | 0 | |
| 4 | Jo | 9:00 | 17:30 | 45 | |
| 5 | Kai | 8:15 | 16:45 | 30 | |
| 6 | Lena | 6:00 | 14:00 | 20 |