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.
Working on a sheet you inherited? Run the Auditor on the whole file first — it flags every #REF!, #N/A, broken column pattern, and inconsistent formula in seconds, free, no signup.
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.