Times and hours worked: practice exercises
Shift lengths, ×24, breaks, past midnight, [h]:mm. 5 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.
In D2, work out the length of each shift. Column D is formatted as h:mm, so 8 and a half hours shows as 8:30.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Employee | Start | End | Length |
| 2 | Priya | 7:00 | 15:45 | |
| 3 | Hamza | 12:30 | 17:15 | |
| 4 | Ben | 9:00 | 17:30 | |
| 5 | Sunil | 10:30 | 15:15 | |
| 6 | Marek | 10:30 | 14:45 | |
| 7 | Liam | 7:00 | 14:00 | |
| 8 | Pippa | 11:15 | 16:30 | |
| 9 | Rachel | 6:15 | 16:00 |
How it works
A spreadsheet stores a time as a fraction of a day: 12:00 is 0.5 and 06:00 is 0.25. Subtracting one time from another gives a length of time, which shows as h:mm when the cell is formatted that way.
To turn a length of time into hours for pay, multiply by 24: 7:30 becomes 7.5. Break minutes divide by 60 to become hours.
A shift that ends after midnight has an end time smaller than its start, so C2-B2 comes out negative. MOD(C2-B2,1) adds the missing day back. Totals of 24 hours or more need the [h]:mm format, or 26:15 shows as 2:15.
=D2-C2 =(D2-C2)*24 =MOD(D2-C2,1)*24
Times are fractions of a day
A spreadsheet stores a time as a fraction of a day: 12:00 is 0.5, and 06:00 is 0.25.
- End time minus start time gives a length of time. Formatted as h:mm, it shows as 8:30.
- For pay you usually need decimal hours (8.5). Multiply a length of time by 24, the hours in a day.
- Break minutes become hours by dividing by 60.
Worked example: shift lengths
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Shift | Start | End | Length | Hours |
| 2 | Amira | Early | 6:00 | 14:30 | ||
| 3 | Ben | Late | 14:00 | 22:15 | ||
| 4 | Cara | Night | 22:00 | 6:00 |
=D2-C2in E2. End minus start gives the length of the shift. Column E is formatted as h:mm, so it shows 8:30.=(D2-C2)*24in F2. For decimal hours, multiply by 24: 8.5. The brackets make it subtract first.
Shifts past midnight
A total of 24 hours or more needs the [h]:mm format. Plain h:mm wraps round, so 26:15 would show as 2:15.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Name | Shift | Start | End | Length | Hours |
| 2 | Amira | Early | 6:00 | 14:30 | ||
| 3 | Ben | Late | 14:00 | 22:15 | ||
| 4 | Cara | Night | 22:00 | 6:00 |
=(D4-C4)*24in . Cara's night shift ends at 06:00, an earlier time than its start, so this gives −16.=MOD(D4-C4,1)*24in .MOD(…,1)adds the missing day back, so the length is right: 8 hours.
What you'll practise
- In D2, work out the length of each shift. Column D is formatted as h:mm, so 8 and a half hours shows as 8:30.
- Pay is worked out from decimal hours, so 7 hours 45 minutes needs to be 7.75. In D2, work out each shift's length in decimal hours.
- Breaks are unpaid. In E2, work out each person's paid hours: the shift in decimal hours, less the break, which is in minutes in column D.
- Some of these shifts run past midnight. In D2, work out each shift's length in decimal hours, so that the night shifts come out right too.
- Column D holds each day's shift length as h:mm. In H1, work out the week's total in decimal hours, ready for the timesheet.