Formula for Overtime Hours in Excel: Simple Examples

Formulas & functions · Updated November 20, 2026

For a daily overtime calculation in Excel, use =MAX(0,(MOD(C2-B2,1)-D2)*24-E2). With a start time in B2, end time in C2, an unpaid break in D2, and the regular-hours threshold in E2, the formula returns overtime as a decimal number of hours, including for a shift that crosses midnight.

What the formula calculates

Excel stores times as fractions of a day: one hour is 1/24 of a day. Subtracting a start time from an end time therefore produces a fraction, not a number of hours. The formula uses MOD to calculate elapsed time across midnight, subtracts the break, multiplies the remaining fraction by 24, and then subtracts the regular-hours threshold. MAX(0,…) prevents a negative overtime result on a shorter day.

For example, suppose B2 is 8:00 AM, C2 is 6:30 PM, D2 is 0:30 for a half-hour unpaid break, and E2 contains 8. The elapsed shift is 10.5 hours; after the break, paid work time is 10 hours; after the eight-hour threshold, the result is 2 overtime hours. Enter times as Excel-recognized times, such as 8:00 AM, and enter E2 as the number 8, not as 8:00.

The formula describes a calculation rule, not a universal definition of overtime. A workplace may use daily limits, weekly limits, different thresholds, or rules for particular types of work. Set the threshold and the hours included in the calculation to match the policy that applies to your situation.

Set up the worksheet

In a simple daily log, use one row per employee per shift. Put the start time in B, end time in C, unpaid break duration in D, regular-hours threshold in E, and overtime result in F. Give the columns clear headings so someone reviewing the sheet can tell whether a break is entered as a duration and a threshold as a decimal number.

  • Enter B2 and C2 as time values, not text that merely looks like a time.
  • Enter D2 as a time duration, such as 0:30 for 30 minutes. A blank D2 is treated as zero in this calculation.
  • Enter E2 as a number of hours, such as 8 or 7.5.
  • Put the formula in F2, then use the fill handle or copy and paste to apply it to other rows.

To enter a time duration that might exceed 24 hours, use a format such as [h]:mm; ordinary clock formats can wrap after 24 hours. For the decimal overtime result, select the result cells and choose Home > Number Format, then select Number and set the desired decimal places. A result of 1.5 means one and a half hours, or 1 hour and 30 minutes.

Calculate overtime as a time duration

Some timesheets are easier to read when overtime displays as hours and minutes rather than a decimal. Use the same inputs, but do not multiply the result by 24. Format the result as [h]:mm so Excel shows a duration rather than a time of day.

With a start time in B2, end time in C2, unpaid break duration in D2, and regular-hours threshold in E2 expressed as decimal hours, enter =MAX(0,MOD(C2-B2,1)-D2-E2/24) in F2. For the example above, the underlying duration is 2 hours, and the [h]:mm display is 2:00. The E2/24 part converts the threshold from decimal hours to Excel’s fraction-of-a-day time value.

Do not apply a clock format such as h:mm when the result may reach 24 hours or more. It can make a duration look as if it has reset to zero. The custom format [h]:mm keeps accumulating hours; decimal formatting is usually more convenient for adding payroll totals.

Handle overnight shifts and long shifts

When start and end entries contain times only, a straightforward subtraction can be negative for a shift that crosses midnight. MOD(C2-B2,1) treats the difference as a time within a 24-hour day. If a shift starts at 10:00 PM and ends at 6:30 AM, with no break and an eight-hour threshold, the elapsed time is 8.5 hours and the daily formula returns 0.5 overtime hours.

That approach assumes a shift lasts less than 24 hours. Because MOD wraps differences to a one-day cycle, it cannot distinguish a 25-hour elapsed shift from a one-hour difference when the cells contain times only. For unusually long shifts, enter full date-and-time values in B and C so the dates preserve the actual elapsed days.

With full date-and-time values in B2 and C2, a break duration in D2, and a decimal-hours threshold in E2, use =MAX(0,((C2-B2)-D2)*24-E2). For example, if B2 is November 2 at 8:00 AM and C2 is November 3 at 10:00 AM, the elapsed time is 26 hours before breaks. This version calculates the actual date-time difference instead of wrapping at midnight. Use genuine Excel date-time values; text entries may not subtract correctly.

Calculate weekly overtime

When the rule is based on a weekly threshold, first total the relevant hours for each employee in each defined week, then compare that total with the threshold. A useful data layout has employee names in A2:A100, work dates in B2:B100, and hours worked in F2:F100. Put the employee to summarize in G2, the first date of the week in H2, and the weekly threshold in I2.

With those columns, where F contains hours after unpaid breaks and each date in B is an actual Excel date, enter =MAX(0,SUMIFS($F$2:$F$100,$A$2:$A$100,G2,$B$2:$B$100,">="&H2,$B$2:$B$100,"<"&H2+7)-I2). If that employee worked 44 hours in the seven-day period starting at H2 and I2 is 40, the formula returns 4.

The date criteria include H2 and exclude H2+7, so the selected period is seven days starting with the date in H2. Choose the week start to match the schedule being summarized; a calendar week and a workweek do not necessarily start on the same day. This formula reports hours above a single weekly threshold. If a policy also counts daily overtime, do not automatically add daily results to this weekly result: overlapping hours can be counted twice. The correct treatment depends on the applicable rules.

Check inputs, rounding, and formatting

Small input problems can change a result substantially. Confirm that times are stored as numeric time values, that breaks are entered consistently, and that the threshold is expressed in the units expected by the formula. A break entered as the number 30 means 30 days to Excel, not 30 minutes; enter 0:30 as a time duration instead. If a cell is left blank unintentionally, the calculation may still produce a number, so consider adding a separate completeness check or requiring all necessary entries before using the totals.

Decide how your source records handle rounding before building it into the spreadsheet. Rounding each punch, each shift, or a weekly total can produce different totals. Do not assume that a convenient rounding method matches an organization’s policy or a legal requirement. Keep the original recorded times if you need to audit how a rounded total was produced.

For a quick visual review, format the overtime column as Number with one or two decimal places and add a heading such as Overtime (hours). Use consistent number formats across the sheet; mixing decimal hours with hh:mm durations makes totals easy to misread. A decimal value of 0.25 represents 15 minutes, while a duration displayed as 0:15 represents the same amount of time.

Common errors and how to fix them

  • Negative hours for an overnight shift: If entries contain times only, use MOD as shown above. For shifts over 24 hours, use date-and-time entries and direct subtraction.
  • A result that looks like a time of day: Format decimal results as Number, or format duration results as [h]:mm. The formula’s output and the cell’s display format are separate things.
  • Unexpectedly large or small totals: Check whether the break is a time duration and the threshold is a decimal number. A threshold entered as 8:00 is a time value, not the number 8 expected by the decimal-hours formula.
  • Overtime appears on every row: Check that E2 contains the intended threshold. A missing or zero threshold makes any positive paid hours exceed it.
  • The total differs from a payroll report: Confirm the reporting week, qualifying hours, break treatment, rounding, and overtime rules. A spreadsheet can only calculate the inputs and rule it was given.

Choose daily or weekly calculations

Use a daily formula when the rule you need to model compares each shift or day with a daily threshold. Use a weekly summary when the rule compares accumulated hours in a defined workweek. If both kinds of rules apply, build them as separate, clearly labeled calculations and determine how overlapping hours should be treated before combining results.

A daily row is easy to inspect because its inputs and result sit together. A weekly summary is more useful when many shifts contribute to one total, but it depends on consistent employee names, valid work dates, and a clearly defined week. For a larger workbook, a formatted Excel table can make it easier to add rows while keeping headings and formulas organized. Whichever method you choose, preserve the underlying time records and label the result as an estimate of hours under the selected rule, rather than treating the formula as a payroll ruling.

Frequently Asked Questions

How do I calculate overtime after 40 hours in Excel?
Sum the employee’s qualifying hours for the chosen seven-day workweek, then subtract 40 and use MAX to prevent a negative result. The weekly SUMIFS example above does this; change the threshold cell to the applicable number and set the correct week start.

How do I subtract a lunch break?
Enter the unpaid break as an Excel time duration, such as 0:30 for 30 minutes, then subtract that cell from the elapsed shift before comparing hours with the threshold. Paid breaks should not be deducted unless the rule you are modeling calls for it.

Why does Excel show a decimal instead of hours and minutes?
The daily formula multiplies elapsed time by 24, so its result is decimal hours. Format it as Number to read values such as 1.5, or use the duration version of the formula and format the cell as [h]:mm.

Can I use the same formula for every employee?
Yes, if each row uses the same column layout and the appropriate threshold and break values. Copy the formula down, then verify that its cell references point to the intended row and that any different employee schedules are represented correctly.

Does an Excel overtime formula determine what overtime is legally owed?
No. It performs arithmetic using the inputs and threshold you specify. Applicable definitions can depend on the workplace, location, agreement, and type of work, so verify the rules with an appropriate authoritative source or qualified professional.

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.