The Overtime Formula for Google Sheets (2026)

The Short Version

A weekly overtime sheet needs four columns and three formulas. Put total paid hours in one cell, your rate in another, and let the sheet split the week at 40. If you would rather skip the spreadsheet entirely, the overtime pay chart does the same job for sixteen rates at once.

The Sheet Layout

| Cell | Contents |

|---|---|

| B1 | Hourly rate, e.g. 18.50 |

| B2 | Total paid hours for the week |

| B3 | Regular hours |

| B4 | Overtime hours |

| B5 | Gross pay |

The Three Formulas

Regular hours (B3):

```

=MIN(B2,40)

```

Overtime hours (B4):

```

=MAX(0,B2-40)

```

Gross pay (B5):

```

=ROUND(B3*B1 + B4*B1*1.5, 2)

```

At $18.50 and 47 paid hours the sheet returns 40 regular hours, 7 overtime hours, and $934.25 gross. The ROUND wrapper matters: without it, fractional cents accumulate across a year of pay periods and your totals stop matching payroll.

Turning Clock Times Into Hours

If you log start and end times as text like 07:00 and 19:30, Sheets stores them as fractions of a day. Convert with:

```

=(C2-B2)*24 - D2

```

Where C2 is the end time, B2 the start time, and D2 the unpaid meal period in hours (0.5 for a 30-minute lunch). Format the result cell as a plain number, not duration, or the multiplication downstream will misbehave. A shift crossing midnight needs:

```

=IF(C2

```

Adding Double Time

For California-style daily rules, add a per-day row and split each day rather than the week:

```

=MIN(E2,8) ' regular hours that day

=MAX(0,MIN(E2,12)-8) ' 1.5x hours

=MAX(0,E2-12) ' 2x hours

```

Then total the three columns for the week and price them at 1x, 1.5x, and 2x. The double time calculator is a faster sanity check on the same numbers.

Blending a Shift Differential

If part of the week is paid at a premium rate, overtime must be based on the weighted average. Compute total straight-time earnings, divide by total hours, and use that as the regular rate:

```

=ROUND((F2 + F3) / (E2 + E3), 4)

```

Then the overtime premium is that regular rate times 0.5 times the overtime hours, added to the straight-time total. Keep four decimal places on the regular rate — rounding it to cents before multiplying is where blended-rate sheets drift. The weighted average overtime guide walks through the full method.

Three Mistakes That Break Overtime Sheets

• . Averaging two weeks. Overtime is calculated per workweek. A 30-hour week followed by a 50-hour week owes 10 overtime hours, not zero.
• . Counting unpaid meal periods. Strip bona fide 30-minute-plus breaks before the hours reach column B.
• . Using base rate instead of regular rate. Differentials and non-discretionary bonuses have to be inside the rate before the 1.5 multiplier.

Working in Excel instead? The Excel overtime formula guide covers the same logic with Excel syntax and a downloadable structure.