Subtracting a clock-in time from a clock-out time gives the length of a shift, until the shift runs past midnight and the answer turns negative. Excel stores a time as a fraction of a day, so MOD with a divisor of 1 wraps that negative gap back round the clock into the hours actually worked.
You run payroll for a care home that staffs around the clock. Last Saturday's timesheet is in A2:D7: when each person clocked in, when they clocked out, and how many minutes of unpaid break they took. Three of the shifts finished the next morning. In E2, work out the paid hours for the shift as a decimal number of hours, so 8 hours 15 minutes reads 8.25, with the unpaid break taken off. Use one formula that is right for day shifts and overnight shifts alike, and copy it down to E7.
Solve it on your own to keep the bonus. Each hint gets one step closer to the formula.
This is the grid you start with. Cell references in the task — B6, C2 — point at the row numbers and column letters below.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Staff member | Clock in | Clock out | Unpaid break (min) | Paid hours |
| 2 | Dana Okafor | 7:00 | 15:30 | 30 | |
| 3 | Sofia Lindqvist | 14:00 | 22:15 | 30 | |
| 4 | Luis Ferreira | 22:00 | 6:30 | 30 | |
| 5 | Priya Natarajan | 19:00 | 7:15 | 45 | |
| 6 | Tom Brannigan | 23:30 | 8:00 | 60 | |
| 7 | Aisha Rahman | 6:45 | 18:15 | 45 |
A clock face has no idea which day it is. Luis clocks in at 22:00 and out at 6:30, and to Excel those are 0.917 and 0.271 of a day, so C4-B4 is -0.646, a shift that apparently ended fifteen and a half hours before it began. Payroll would either pay nothing or throw the total out completely. What the subtraction is missing is the midnight in between: the real gap is that negative number plus one whole day. MOD(x,1) adds exactly that, and only when it is needed. For a positive gap like Dana's 0.354 it changes nothing, because the remainder of a fraction divided by 1 is the fraction itself. For a negative gap between -1 and 0 it adds one whole day, so -0.646 becomes 0.354, the eight and a half hours Luis worked. One formula therefore handles both kinds of shift without an IF to test which is which. Multiplying by 24 turns a fraction of a day into hours, which is what an hourly rate is applied to, and the break is divided by 60 because it was recorded in minutes. Priya's twelve-hour night, from 19:00 to 7:15 less 45 minutes, comes out at 11.5 paid hours. The method only fails for a shift of 24 hours or more, which a clock-in and a clock-out time alone could never describe; that needs dates as well as times.