Spreadsheet Practice

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

Formula (Excel and Google Sheets)

=MAX(0,(C2-B2)*24-$G$1)

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

Result on the example
one result per row in D2, filled down to D6
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in D2, filled down to D6. Change any input and the result updates, or edit the formula to experiment.

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.

=MAX(0,(C2-B2)*24-$G$1)

Result0.5

  1. C23:30 PM

    The value in C2.

  2. B27:00 AM

    The value in B2.

  3. C2-B20.3541666667

    The - operator subtracts.

  4. C2-B2)*248.5

    The * operator multiplies.

  5. $G$18

    The value in G1.

  6. C2-B2)*24-$G$10.5

    The - operator subtracts.

  7. MAX(0,(C2-B2)*24-$G$1)0.5

    MAX: Largest number.

    • number10
    • number2C2-B2)*24-$G$10.5

In Google Sheets

The same formula works in Google Sheets: =MAX(0,(C2-B2)*24-$G$1). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=MAX(0,(C2-B2)*24-8)0.50, 3.25, 0.00…With the 8 typed in. A cell is easier to change.
=MIN((C2-B2)*24,$G$1)8.00, 8.00, 6.00…Different resultThe 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.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Calculate overtime hours over 8 hours a day

In D2, calculate the hours worked beyond the regular hours in G1, showing 0 for shorter days. Fill down to D6.

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).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.