How to Calculate Overtime and Gross Pay in Excel & Google Sheets

Under the US Fair Labor Standards Act (FLSA), non-exempt employees must receive overtime pay at a rate not less than one and one-half times (1.5x) their regular rate of pay for all hours worked over 40 in a workweek.

This guide demonstrates how to calculate regular hours, overtime, California daily double-time rules, and total gross payroll using robust, error-proof spreadsheet formulas.

---

The Standard Weekly Overtime Formula

Instead of complex nested IF statements, the cleanest and most reliable method uses MIN and MAX:

=MIN(40, Total_Hours) * Rate + MAX(0, Total_Hours - 40) * (Rate * 1.5)

Why MIN and MAX Are Superior to IF:

• MIN(40, Total_Hours) guarantees that regular hours never exceed 40.
• MAX(0, Total_Hours - 40) returns 0 if hours are 40 or fewer, eliminating negative overtime numbers without needing logic tests.

---

Weekly Payroll Model Example

Here is a typical weekly payroll tracking sheet for hourly employees:

Row	Employee (A)	Hours Worked (B)	Hourly Rate (C)	Regular Hours (D)	Overtime Hours (E)	Gross Pay (F)
2	Sarah Jenkins	38.0	$24.00	=MIN(40, B2)	=MAX(0, B2-40)	=(D2*C2)+(E2*C2*1.5)
3	Michael Chang	45.5	$32.50	=MIN(40, B3)	=MAX(0, B3-40)	=(D3*C3)+(E3*C3*1.5)
4	David Ross	52.0	$28.00	=MIN(40, B4)	=MAX(0, B4-40)	=(D4*C4)+(E4*C4*1.5)

Calculated Results:

• Sarah Jenkins (38.0 hrs): Regular: 38.0, OT: 0.0, Gross Pay: $912.00
• Michael Chang (45.5 hrs): Regular: 40.0, OT: 5.5, Gross Pay: $1,568.13
• David Ross (52.0 hrs): Regular: 40.0, OT: 12.0, Gross Pay: $1,624.00

---

Converting Time Clock Stamps to Decimal Hours

If your timesheet records exact punch times (e.g., 8:15 AM to 5:00 PM with a 30-minute lunch break):

Excel and Google Sheets store time as a fraction of a 24-hour day (e.g., 12 hours = 0.5). To convert time differences to decimal hours, multiply by 24:

= (Clock_Out - Clock_In - Lunch_Break) * 24

Time Clock Calculation Table:

Clock In (A)	Clock Out (B)	Lunch Break (C)	Total Decimal Hours Formula (D)	Decimal Hours
08:00 AM	05:00 PM	00:30	=(B2 - A2 - C2) * 24	8.50 hrs
07:45 AM	04:30 PM	00:45	=(B3 - A3 - C3) * 24	8.00 hrs
09:00 AM	06:30 PM	00:30	=(B4 - A4 - C4) * 24	9.00 hrs

[!IMPORTANT] Format the Output Cell as Number: If the result displays as 12:00 AM or a time format, change the cell number format to General or Number with 2 decimals (Format > Number > Number).

---

Handling Overnight Night Shifts

When an employee clocks in at 10:00 PM and clocks out at 6:30 AM the next morning, Clock_Out - Clock_In results in a negative number.

To handle overnight shifts seamlessly without errors, use MOD:

=(MOD(Clock_Out - Clock_In, 1) - Lunch_Break) * 24

• Example: Clock In 22:00, Clock Out 06:00, Lunch 0.5 hr $\rightarrow$ returns 7.50 hours accurately.

---

Advanced: California Daily Overtime & Double-Time

California labor law mandates:

1. Regular Pay: First 8 hours in a workday.
2. Overtime (1.5x): Hours worked between 8 and 12 in a workday.
3. Double Time (2.0x): Hours worked beyond 12 in a workday.

If daily hours are in cell B2:

• Regular Hours (8 max):

  =MIN(8, B2)

• 1.5x Overtime (hours 8 to 12):

  =MIN(4, MAX(0, B2 - 8))

• 2.0x Double Time (hours 12+):

  =MAX(0, B2 - 12)

• Total Daily Gross Pay:

  =(MIN(8, B2)*Rate) + (MIN(4, MAX(0, B2-8))*Rate*1.5) + (MAX(0, B2-12)*Rate*2.0)

---

Summary Checklist for Accurate Payroll Sheets

1. Always format hours worked as Number (2 decimal places), not Time.
2. Multiply time stamps by 24 to get decimal numbers.
3. Use MOD(Out - In, 1) to safeguard against overnight shift calculations.
4. Verify overtime rules based on state/federal jurisdiction (weekly 40h vs. daily 8h).