Split Hours into Regular and Overtime

All versionsMINMAX

On a timesheet, hours over a daily threshold are overtime. Splitting a day’s total into regular and overtime hours is a tidy MIN/MAX pair — no nested IFs.

Quick formula: for total hours in A2 and an 8-hour threshold:

=MIN(A2, 8) // regular hours =MAX(A2 - 8, 0) // overtime hours

MIN(A2, 8) caps regular hours at 8; MAX(A2-8, 0) is whatever’s left over (never negative).

Functions used (tap for the full reference guide):

MIN MAX

The example

Daily hours split at an 8-hour line.

	A	B	C
1	Hours	Regular	Overtime
2	7.5	7.5	0
3	10	8	2
4	8	8	0

The formula

Regular in B2, overtime in C2:

=MIN(A2, 8) → regular =MAX(A2 - 8, 0) → overtime

How it works

Two simple caps do the split:

MIN(A2, 8) gives the regular hours: if the day is 10 hours, MIN returns 8; if it’s 7.5, MIN returns 7.5.
MAX(A2 - 8, 0) gives the overtime: hours above 8, floored at 0 so short days show no negative overtime.
Regular + overtime always equals the total — the split is exact.
Multiply by pay rates for cost: regular*rate + overtime*rate*1.5.

Working with clock times? If A2 is a start/end difference stored as a time, convert to hours first ((end-start)*24), then apply the MIN/MAX split.

Try it: interactive demo

Live demo

Set the day’s hours and the overtime threshold.

Hours (A2)

Threshold

Regular: Overtime:

Variations

From clock times

Convert a time difference to hours first:

=MIN((End-Start)*24, 8)

Weekly overtime (over 40)

Split a weekly total at 40:

=MAX(WeeklyTotal - 40, 0)

Pay with a 1.5x OT rate

Total pay in one formula:

=MIN(A2,8)*Rate + MAX(A2-8,0)*Rate*1.5

Pitfalls & errors

Negative overtime. Forgetting the , 0) in MAX lets short days show negative overtime. The MAX floor prevents it.

Times vs numbers. If hours are stored as clock times (a fraction of a day), multiply by 24 before the split, or the thresholds compare wrong.

Daily vs weekly rules differ. Some policies compute overtime weekly (over 40), not daily (over 8). Use the rule that matches your jurisdiction/contract.

Practice workbook

📊

Download the free Split Hours into Regular and Overtime practice workbook

A daily hours table with live regular/overtime split, the clock-time, weekly, and pay-rate variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I split hours into regular and overtime in Excel?

Use =MIN(A2, 8) for regular hours and =MAX(A2-8, 0) for overtime, where 8 is the daily threshold. The MAX floor of 0 prevents negative overtime on short days.

How do I calculate overtime from clock-in and clock-out times?

Convert the time difference to hours first with (End-Start)*24, then apply =MIN(hours, 8) and =MAX(hours-8, 0).

How do I compute pay with a 1.5x overtime rate?

Combine the split with rates: =MIN(A2,8)*Rate + MAX(A2-8,0)*Rate*1.5 gives total pay for the day.

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

Function references: MIN · MAX