How to calculate hours worked (and overtime) in Excel

How Excel stores times (and why you multiply by 24)

In Excel a time is a fraction of a day: 6:00 AM is 0.25, noon is 0.5, 6:00 PM is 0.75. Subtracting 8:30 AM from 5:00 PM gives 0.354, which Excel shows as 8:30 if the cell has a time format. That is fine for display, but you cannot multiply it by an hourly rate directly: 0.354 x $22.50 is about $8.

Multiply the difference by 24 to turn it into decimal hours: =(C2-B2)*24 gives 8.5. Format that cell as Number, not Time. If you want to keep times on screen and total them, use the custom format [h]:mm. The square brackets let totals run past 24 hours; plain h:mm wraps a 43.5-hour week back to 19:30.

The examples below already use decimal hours (8.5 means 8:30, 17.25 means 5:15 PM), so no *24 is needed. Everything else works the same way.

1. Type times with a colon, such as 8:30 AM or 17:00, so Excel recognizes them as times.
2. For hours worked, use =(C2-B2)*24 and format the result as Number with 2 decimals.
3. For a time-style total, right-click the cell > Format Cells > Custom and type [h]:mm.

Hours for one shift, minus the break

End minus start minus break gives paid hours for the day. Monday runs 8:30 to 5:00 with a 30-minute unpaid break. Fill the formula down for the rest of the week.

With real time values, a break typed as 0:30 is already a fraction of a day, so the formula becomes =(C2-B2-D2)*24. If the break is typed in minutes (30), divide it: =(C2-B2)*24-D2/60.

Night shifts that end after midnight

A shift from 10 PM to 6 AM gives a negative number if you just subtract, because 6 is smaller than 22. MOD wraps the result around the clock. With decimal hours use MOD(out-in,24). With real Excel times use =MOD(out-in,1)*24, because a day is 1 in time values.

MOD only works for shifts shorter than 24 hours. For longer stretches, enter full date and time in both cells and subtract normally.

Weekly total and overtime hours

You can add a daily hours column and SUM it, or let SUMPRODUCT do the subtraction for every row at once. The week totals 43.5 hours.

Overtime is whatever is above your threshold. MAX(0, ...) stops a short week from producing negative overtime. The threshold of 40 a week is common, but rules differ by country, state and contract (some count overtime per day), so put the number your employer uses in a cell if it might change.

Daily overtime (hours over 8 each day)

If overtime is counted per day instead, compare each day to 8 and add only the excess. The (hours>8) part is TRUE or FALSE, which SUMPRODUCT treats as 1 or 0, so days under 8 contribute nothing.

Gross pay with time-and-a-half

Pay regular hours at the base rate and overtime at 1.5 times the rate. MIN(40, hours) caps the regular part; MAX(0, hours-40) is the overtime part. Here the hourly rate is in F2. ROUND to 2 decimals so the pay matches what a payroll system would print.