Excel Formula for Calculating Overtime Hours

Formulas & functions · Updated November 19, 2026

To calculate daily overtime in Excel, subtract unpaid break hours and the regular-hours threshold from the shift length, then keep the result from going below zero. With a start time in A2, end time in B2, break duration in hours in C2, and the daily threshold in E1, use =MAX(0,MOD(B2-A2,1)*24-C2-$E$1).

What the overtime formula calculates

Excel stores a time as a fraction of a 24-hour day. For example, a difference of 0.5 represents 12 hours, not 12 as a numeric value. Multiplying the time difference by 24 converts it to hours that are easier to compare with an overtime threshold.

The formula uses MOD(B2-A2,1) to calculate the elapsed time between the start and end, including a shift that passes midnight. It subtracts C2, the unpaid break entered as a number of hours, and then subtracts E1, the number of hours treated as regular for that shift. MAX(0,...) returns zero instead of a negative number when the shift does not exceed the threshold.

For example, enter 8:00 AM in A2, 6:30 PM in B2, 0.5 in C2, and 8 in E1. The shift spans 10.5 hours; after a half-hour break, worked time is 10 hours. Subtracting the 8-hour threshold leaves 2 overtime hours.

Set up the worksheet consistently

Use one row for each shift, and keep time entries, break durations, and thresholds in clearly labeled columns. A practical layout is:

  • A: Start time
  • B: End time
  • C: Unpaid break, in decimal hours
  • D: Overtime hours, calculated by the formula
  • E1: Regular-hours threshold, such as 8

Enter start and end values as Excel times, such as 8:00 AM or 6:30 PM. Enter breaks as decimal hours: 0.5 means 30 minutes, 0.75 means 45 minutes, and 1 means one hour. Do not enter 30 to mean a 30-minute break when the formula expects hours. If you prefer to enter break duration as a time value, such as 0:30, convert it to hours by multiplying by 24 in the formula; do not mix the two input methods.

Put a descriptive label next to the threshold, and use an absolute reference such as $E$1 so it stays fixed when you fill the formula down. After entering the first formula in D2, copy it to the other shift rows. The same formula can then use each row’s start, end, and break cells while continuing to use the threshold in E1.

Calculate overtime for a shift

With start time in A2, end time in B2, unpaid break hours in C2, and the daily regular-hours threshold in E1, enter this in D2:

=MAX(0,MOD(B2-A2,1)*24-C2-$E$1)

Using the example above, the formula returns 2. If the shift is 7:00 AM to 3:30 PM with a 0.5-hour unpaid break, worked time is 8 hours and overtime is 0. If the shift is 9:00 AM to 7:00 PM with a one-hour unpaid break, worked time is 9 hours and the result is 1.

These examples treat the break as unpaid time. If a break is paid and counts as time worked under the applicable rules or company policy, do not subtract it from the hours used for overtime. The worksheet should reflect how the hours are actually classified, not simply the time someone was away from the clock.

Handle overnight shifts and date entries

The MOD function is useful for an overnight shift because a time-only end entry can appear numerically earlier than the start entry. For example, a start at 10:00 PM and an end at 6:00 AM produces an 8-hour elapsed time with the formula above, rather than a negative duration. With no unpaid break and an 8-hour threshold, the formula returns 0 overtime hours.

This approach assumes the shift lasts less than 24 hours. If a shift can last 24 hours or longer, or if exact calendar dates matter, enter full date-and-time values in both cells. With full timestamps, and a shift duration under 24 hours, the elapsed-time portion can use B2-A2 instead of MOD(B2-A2,1). That preserves the date difference and avoids treating an elapsed 24-hour period as zero.

Overnight work can also raise questions about which workday or workweek receives the hours. A spreadsheet formula cannot decide that policy. Keep the recorded start and end dates, and confirm how the applicable payroll rules define the workday and workweek before using totals for payroll.

Calculate overtime against a weekly threshold

Some overtime calculations depend on total hours in a workweek rather than a separate threshold for each shift. First calculate each shift’s worked hours, excluding unpaid breaks where appropriate, and sum the hours for the defined workweek. If daily worked-hour totals are in D2:D7, with one row per day, this formula returns hours above 40:

=MAX(0,SUM(D2:D7)-40)

If the six entries total 45 hours, the formula returns 5. If they total 38 hours, it returns 0. The range must cover the whole relevant workweek and must not accidentally include days from another week. Replace 40 with the applicable threshold if the calculation calls for a different one.

Do not automatically add daily overtime and weekly overtime totals together. The same hours may satisfy more than one threshold, and the correct treatment depends on the governing rules and the employer’s policy. This simple weekly formula reports only the amount over the weekly threshold; it does not determine how overlapping daily and weekly rules should be reconciled.

Format and check the results

Because the formula multiplies by 24, it returns ordinary decimal hours. Format the overtime result cells as Number, not Time. In Excel for Microsoft 365 on Windows, select the result cells and choose Home > Number Format > Number. Set a suitable number of decimal places, such as two if partial hours are recorded. On Mac, the formula is the same; the number-format controls are also on the Home tab, though the ribbon layout can vary slightly by release.

If you want to show elapsed time as hours and minutes instead, use a time-duration result rather than a decimal-hours result. That requires a different formula approach and a duration format such as [h]:mm; the square brackets allow totals greater than 24 hours to display cumulatively. Do not apply that format to the decimal result above: Excel would interpret the number as a fraction of a day, so a value of 2 could display as 48 hours.

Check a few rows by hand before relying on a filled-down column. Verify that the source cells are recognized as times, breaks use the expected units, and a shift below the threshold returns zero. Also confirm the weekly date boundaries and whether paid breaks count as worked time.

Common formula problems

  • A negative or unexpectedly large answer: Check that start and end cells contain real Excel times, not text. For overnight work, confirm that the formula includes MOD. A mistaken AM/PM entry can also make a shift much longer or shorter than intended.
  • Every result is zero: Check that the threshold cell contains a number such as 8, rather than a time value such as 8:00 AM. Confirm that unpaid breaks are not entered in minutes when the formula expects decimal hours.
  • A result displays as a time: Change the result’s number format to Number or General. The formula returns hours as a decimal, not an Excel time-of-day value.
  • Too many decimal places: A time entry may include minutes that produce a fractional hour. Choose the display precision that matches the organization’s timekeeping and rounding rules; formatting changes what is shown, not the underlying value.
  • Totals differ from payroll: The workbook may be using the wrong threshold, week boundary, break treatment, or rounding method. Confirm those rules before treating the spreadsheet total as a payroll figure.

Choose a formula that matches the rule

There is no single overtime threshold that applies to every workplace or jurisdiction. A daily-hours formula is appropriate only when the rule being applied uses a daily threshold. A weekly formula is appropriate only when the relevant rule uses a weekly total. Some arrangements use additional conditions, different workweek definitions, or special handling for particular kinds of hours.

For a personal estimate or a simple time log, the formulas here make the arithmetic transparent. For an official payroll calculation, verify the threshold, qualifying hours, break treatment, rounding, and workweek definition against authoritative local requirements and the employer’s policy. The spreadsheet calculates the rule you encode; it cannot determine which rule applies.

Frequently Asked Questions

How do I calculate overtime in Excel after 8 hours?
Use the shift formula with 8 in the threshold cell. For example, with start in A2, end in B2, unpaid break hours in C2, and 8 in E1, enter =MAX(0,MOD(B2-A2,1)*24-C2-$E$1) in D2. It returns only hours above eight worked hours.

How do I calculate overtime after 40 hours in a week?
Sum the worked-hour totals for the complete workweek, then subtract 40 and use MAX to prevent a negative result. If the daily totals are in D2:D7, use =MAX(0,SUM(D2:D7)-40). Confirm that the selected rows match the actual workweek.

Can Excel calculate overtime for a shift that ends the next day?
Yes. If the shift is shorter than 24 hours and the cells contain times, MOD in MOD(B2-A2,1) handles an end time earlier than the start time. For longer shifts, enter full date-and-time values instead.

Should I enter a 30-minute break as 0.5 or 0:30?
For the formula in this article, enter 0.5 because the break column is measured in decimal hours. If you enter 0:30 as an Excel time, adjust the formula to convert that time value to hours before subtracting it.

Why does my overtime formula show a time instead of a number?
The result cell is probably formatted as Time. Change it to Number or General to display decimal hours. A value of 2 should appear as 2 hours, not as a time-of-day or a 48-hour duration.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.