Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to calculate working hours excluding a lunch break in Excel and Google Sheets
Excel & Google Sheets
=(B2-A2-C2)*24✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| ClockIn | ClockOut | Lunch | NetHours |
| 0.375 | 0.708333 | 0.041667 | =(B2-A2-C2)*24 |
| 0.333333 | 0.708333 | 0.041667 | =(B3-A3-C3)*24 |
| 0.375 | 0.75 | 0.041667 | =(B4-A4-C4)*24 |
=(B2-A2-C2)*24→6.999984
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Enter the clock-in time in A2, the clock-out time in B2, and the lunch break duration in C2. Format all three cells as Time (h:mm) so Excel stores them as fractions of a day.
- 2In D2, calculate net hours: =(B2-A2-C2)*24. Excel stores times as fractions of 24 hours, so multiplying by 24 converts to decimal hours (e.g. 0.375 × 24 = 9.0 hours).
- 3If your staff sometimes forgets to log out (B2 blank), guard with IF: =IF(B2="","",(B2-A2-C2)*24).
- 4For overnight shifts where clock-out is earlier than clock-in, add MOD: =(MOD(B2-A2,1)-C2)*24. MOD(...,1) wraps negative time differences correctly.
- 5Sum the D column for a weekly total using =SUM(D2:D6), which gives total decimal hours for the week.
Tips
- Entering times as text (e.g. "9:00 AM" from an import) requires conversion with TIMEVALUE before arithmetic. Check with ISNUMBER(A2) — if FALSE, apply TIMEVALUE.
- Format the result cell as Number with 2 decimal places, not as Time, because decimal hours like 8.5 displayed as a Time would show 8:30 AM rather than 8.5 hours.
- For payroll rounding (e.g. to nearest 15 minutes), wrap the result: =ROUND((B2-A2-C2)*24*4,0)/4 rounds to the nearest 0.25 hour.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
Why do I need to multiply by 24?
Excel and Google Sheets store one full day as the number 1. A 9-hour shift is 9/24 ≈ 0.375. Multiplying by 24 converts that fraction back into hours.
How do I handle a variable lunch break duration per employee?
Put each employee's lunch duration in column C alongside their times. The formula =(B2-A2-C2)*24 already references C2 per row, so different durations work automatically.
Can I calculate this without a helper column for the lunch break?
Yes. Hardcode the lunch duration as a time literal: =(B2-A2-TIME(1,0,0))*24 subtracts exactly 1 hour without a separate column.