Confident Cells

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.

Your turn

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.

ABCD
1EmployeeStartEndLength
2Priya7:0015:45
3Hamza12:3017:15
4Ben9:0017:30
5Sunil10:3015:15
6Marek10:3014:45
7Liam7:0014:00
8Pippa11:1516:30
9Rachel6:1516: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

ABCDEF
1NameShiftStartEndLengthHours
2AmiraEarly6:0014:30
3BenLate14:0022:15
4CaraNight22:006:00
  1. =D2-C2 in E2. End minus start gives the length of the shift. Column E is formatted as h:mm, so it shows 8:30.
  2. =(D2-C2)*24 in 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.

ABCDEF
1NameShiftStartEndLengthHours
2AmiraEarly6:0014:30
3BenLate14:0022:15
4CaraNight22:006:00
  1. =(D4-C4)*24 in . Cara's night shift ends at 06:00, an earlier time than its start, so this gives −16.
  2. =MOD(D4-C4,1)*24 in . MOD(…,1) adds the missing day back, so the length is right: 8 hours.

What you'll practise

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.