# How to calculate hours worked minus a lunch break in Excel

Subtract the break from the time on site: `=(C2-B2)*24-D2/60`, with the break in minutes in D2.

**Excel:** `=(C2-B2)*24-D2/60` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Day | In | Out | Break (min) | Hours |
| Mon | 0.3333333333333333 | 0.7083333333333334 | 60 |  |
| Tue | 0.3229166666666667 | 0.6875 | 30 |  |
| Wed | 0.375 | 0.7604166666666666 | 45 |  |
| Thu | 0.3541666666666667 | 0.5208333333333334 | 0 |  |
| Fri | 0.2916666666666667 | 0.65625 | 30 |  |

Formula in E2, filled to E6: `=(C2-B2)*24-D2/60`

Result: 8.00; 8.25; 8.50; 4.00; 8.25

## How it works

`(C2-B2)*24` is the hours from clock-in to clock-out, and `D2/60` turns the break minutes into hours. Subtracting gives the hours worked: on Tuesday that is 8.75 hours on site minus a 30-minute lunch, so 8.25.

## Other ways

- `=(C2-B2-D2/1440)*24`: Subtracts the break as a time first, then converts to hours.
- `=TEXT(C2-B2-D2/1440,"h:mm")`: Hours and minutes as text. (Text in h:mm form.)

## Common mistakes

- A break recorded as a time (0:30) needs *24, not /60: `=(C2-B2-D2)*24`.
- For shifts past midnight, wrap the difference in MOD: `=MOD(C2-B2,1)*24-D2/60`.

## Questions

**How do I handle separate lunch-out and lunch-in punches?**

Add the two worked blocks: `=((lunch_out-in)+(out-lunch_in))*24`.

**How do I deduct 30 minutes only for shifts over 6 hours?**

`=(C2-B2)*24-IF((C2-B2)*24>6,0.5,0)`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/hours-worked-minus-lunch-break