The Case, five steps guide
A week of shifts for eight people, with start and end times and an unpaid break. Work out the hours, round them the way payroll does, find the overtime, total it per person, and name who has the most. One person works nights, and the one with the most hours isn't the one with the most overtime.
Times are fractions of a day
Excel stores 6:00 as 0.25 and 18:00 as 0.75. So 17:30 minus 9:00 is 0.354, a fraction of a day, and it shows as 8:30 only in a time format. To get hours you can calculate with, multiply by 24: =(D2-C2)*24 gives 8.5.
Step by step
- Hours.
=MOD(D2-C2,1)*24-E2/60. A night shift from 22:00 to 6:30 has an end smaller than its start, so D2-C2 is negative; MOD(…,1) adds the missing day. The break is in minutes, so divide it by 60. - Paid hours.
=ROUND(F2*4,0)/4rounds to the nearest quarter: four quarters to an hour, round, divide back. 8.12 hours becomes 8.25; 8.08 becomes 8.MROUND(F2,0.25)does the same. - Overtime.
=MAX(G2-8,0): the part over 8, and 0 for a shorter day instead of a negative number. - Per person.
=SUMIF($B$2:$B$41,J2,$G$2:$G$41), and the same on column H for overtime. - The answer.
=INDEX($J$2:$J$9,MATCH(MAX($L$2:$L$9),$L$2:$L$9,0)).
Where it goes wrong
- No ×24. The hours come out as 0.35 instead of 8.5. Comparing that with 8 is never true, so nobody gets overtime.
- A night shift. Without MOD, the hours are negative.
- The break in minutes. Subtracting E2 takes 30 hours off, not 30 minutes.
- ROUND(F2,2). Two decimal places isn't a quarter hour.
- Overtime from the unrounded hours. 8.08 hours gives 0.08 overtime, but payroll pays 8.
- The most hours instead of the most overtime. Five days just over 8 add up to more hours, and less overtime, than four long days.
The overtime rule is a policy
This case pays overtime for each day over 8 hours, as some employers and some US states do. US federal law counts hours over 40 in a week instead; then the overtime is =MAX(K2-40,0) on the weekly total. Check which rule applies before you build the sheet.
FAQ
- How do I calculate hours worked in Excel?
- Subtract the start time from the end time and multiply by 24: =(D2-C2)*24. Subtract an unpaid break in minutes with -E2/60.
- How do I calculate hours for a night shift that ends after midnight?
- Use MOD: =MOD(D2-C2,1)*24. MOD adds a day when the end time is earlier than the start time.
- How do I round time to the nearest 15 minutes in Excel?
- For decimal hours, =ROUND(F2*4,0)/4 or =MROUND(F2,0.25). For a time value, =MROUND(A2,"0:15").
- How do I calculate overtime in Excel?
- Take the hours over the limit and never less than 0: =MAX(hours-8,0) for a daily rule, or =MAX(weekly_total-40,0) for a weekly one.



































































