Tiered Overtime Pay (1.5x and 2x)

Excel Formulas › HR & Payroll

All versionsMIN

Pay regular hours straight, hours past 40 at time-and-a-half, and hours past 60 at double time. MIN and MAX split the hours into bands so each is paid at the right multiplier.

Quick formula: split hours into 40 reg / next 20 at 1.5x / rest at 2x:
=rate*MIN(h,40) + rate*1.5*MEDIAN(h-40,0,20) + rate*2*MAX(h-60,0)
MIN caps the regular band; MEDIAN clamps the 1.5x band to 0–20; MAX takes only the hours above 60.

Functions used (tap for the full reference guide):

MINMAX

The example

65 hours at $20/hr across three bands.

AB
1HoursGross
265$1,600

The formula

The formula:

=B3*MIN(B2,40) + B3*1.5*MEDIAN(B2-40,0,20) + B3*2*MAX(B2-60,0) // three pay bands

How it works

How it works:

  1. MIN(hours, 40) isolates the regular band, paid at the base rate.
  2. MEDIAN(hours-40, 0, 20) clamps the 1.5× band to between 0 and 20 hours.
  3. MAX(hours-60, 0) takes only the hours above 60 for the 2× band.
  4. Add the three pieces — each set of hours is paid exactly once at its correct multiplier.

MEDIAN as a clamp: MEDIAN(x, low, high) is a neat way to bound a value between two limits — it returns x when in range, the low when below, the high when above. Perfect for capping a pay band at 20 hours without nested IFs.

Try it: interactive demo

Live demo

Hours and base rate.

Gross pay:

Variations

Simple 1.5x over 40

One overtime tier:

=rate*MIN(h,40) + rate*1.5*MAX(h-40,0)

Double over 8/day

Daily OT:

=rate*MIN(h,8) + rate*1.5*MAX(h-8,0)

Overtime hours only

Count OT hours:

=MAX(h-40,0)

Pitfalls & errors

Bands must not overlap. MEDIAN clamps the middle band so hours aren’t double-counted across tiers.

Know your rules. Overtime law varies (weekly vs daily, 1.5× vs 2×) — match your jurisdiction.

Base rate consistency. Use the regular hourly rate; blended rates need a separate calculation.

Practice workbook

📊
Download the free Tiered Overtime Pay (1.5x and 2x) practice workbook
A tiered-overtime sheet with the single-tier, daily-OT, and OT-hours variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I calculate tiered overtime pay in Excel?
Split the hours into bands: =rate*MIN(h,40) + rate*1.5*MEDIAN(h-40,0,20) + rate*2*MAX(h-60,0). Each band is paid once at its multiplier.
How does MEDIAN clamp a pay band?
MEDIAN(h-40, 0, 20) returns the hours above 40 but never less than 0 or more than 20 — bounding the 1.5x band cleanly.
What about simple overtime over 40 hours?
Use =rate*MIN(h,40) + rate*1.5*MAX(h-40,0) for a single time-and-a-half tier.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Min if criteria · Timesheet overtime · Gross to net pay

Function references: MINMAX