# How to calculate overtime hours over 8 hours a day in Excel

Take the hours worked minus 8, but never below zero: `=MAX(0,(C2-B2)*24-$G$1)`, with 8 in G1.

**Excel:** `=MAX(0,(C2-B2)*24-$G$1)` (Every current version)

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

## Example

Sample data (row 1 is the header):

| A | B | C | D | E | F | G |
| --- | --- | --- | --- | --- | --- | --- |
| Day | In | Out | Overtime |  | Regular hours/day | 8 |
| Mon | 0.2916666666666667 | 0.6458333333333334 |  |  |  |  |
| Tue | 0.2916666666666667 | 0.7604166666666666 |  |  |  |  |
| Wed | 0.3333333333333333 | 0.5833333333333334 |  |  |  |  |
| Thu | 0.28125 | 0.6458333333333334 |  |  |  |  |
| Fri | 0.3125 | 0.6458333333333334 |  |  |  |  |

Formula in D2, filled to D6: `=MAX(0,(C2-B2)*24-$G$1)`

Result: 0.50; 3.25; 0.00; 0.75; 0.00

## How it works

`(C2-B2)*24` is the hours worked that day. Subtracting the 8 regular hours in G1 leaves the overtime, and `MAX(0, …)` turns short days into 0 instead of negative overtime. Tuesday, 7:00 AM to 6:15 PM, is 11.25 hours, so 3.25 hours of overtime.

## Other ways

- `=MAX(0,(C2-B2)*24-8)`: With the 8 typed in. A cell is easier to change.
- `=MIN((C2-B2)*24,$G$1)`: The regular hours instead, capped at 8. (Regular hours, not overtime.)

## Common mistakes

- Overtime rules differ. US federal law counts overtime per week, over 40 hours; some states, such as California, also count it per day over 8.
- Subtract unpaid breaks before comparing with 8.

## Questions

**How do I calculate weekly overtime over 40 hours?**

`=MAX(0,SUM(hours)-40)`.

**How do I get the regular hours?**

`=MIN((C2-B2)*24,$G$1)`.

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/overtime-hours-over-8-hours-a-day