To calculate daily overtime in Excel, put a shift’s start time in C2, end time in D2, and unpaid break length in E2 as decimal hours; calculate worked hours in F2 with =MAX(0,MOD(D2-C2,1)*24-E2), then calculate overtime in G2 with =MAX(0,F2-8) for an eight-hour daily threshold. This setup handles shifts that cross midnight. Confirm your employer’s overtime rules first, because thresholds and break rules vary.
What You’ll Need
- Microsoft Excel for Microsoft 365 on Windows. The formulas below also work in current Excel for Mac and Excel for the web.
- A worksheet with one row per employee per shift, including start time, end time, and unpaid break length.
- The overtime threshold that applies to your calculation, such as eight hours in a workday. Use the applicable company policy and rules for your location rather than assuming this threshold applies everywhere.
- Excel-readable time values. Enter times such as 9:00 AM or 6:30 PM, not text that only looks like a time.
Step-by-Step
- Create clear column headings. In row 1, enter Employee in A1, Date in B1, Start in C1, End in D1, Unpaid break (hours) in E1, Worked hours in F1, and OT hours in G1. Use one row for each employee’s shift. Keeping shifts on separate rows makes it easier to check entries and summarize a week later.
- Enter the shift details. For example, enter a start time of 9:00 AM in C2 and an end time of 6:30 PM in D2. Enter the unpaid break as a decimal number of hours in E2: for a 30-minute break, enter 0.5; for a 45-minute break, enter 0.75. Paid breaks should not be subtracted if your policy counts them as working time. Enter actual Excel time values, not text with extra words such as “9 am shift.”
- Format the time and hour columns. Select columns C:D, then choose Home > Number Format and select Time. A time format such as 1:30 PM makes shift entries easy to read. Select columns E:G and choose Home > Number Format > Number; set two decimal places if you want to see portions of an hour. The worked-hours and overtime results are decimal hours, so 1.5 means one hour and 30 minutes.
- Calculate worked hours after breaks. With start time in C2, end time in D2, and unpaid break hours in E2, enter this in F2:
=MAX(0,MOD(D2-C2,1)*24-E2). MOD handles an end time earlier than the start time by treating it as a shift that crossed midnight. Multiplying by 24 converts Excel’s fraction-of-a-day time result into hours. MAX prevents a negative result if a break entry is longer than the shift, though you should still correct that entry. - Check the result with a simple example. With C2 equal to 9:00 AM, D2 equal to 6:30 PM, and E2 equal to 0.5, the elapsed time is 9.5 hours. Subtracting the 0.5-hour unpaid break gives 9 worked hours, so F2 should display 9.00. If Excel shows a time such as 9:00 AM instead, change the number format for F2 to Number.
- Calculate overtime using your threshold. For a daily threshold of eight hours, with worked hours in F2, enter this in G2:
=MAX(0,F2-8). In the example, 9 worked hours minus the eight-hour threshold gives 1.00 overtime hour. A shift of 7.5 hours returns 0, because the formula does not return a negative overtime amount. Replace 8 with the correct threshold for your situation. - Fill the formulas down the shift list. Select F2:G2 and drag the small fill handle at the lower-right corner of the selection down the rows you need. Excel adjusts the row references automatically. You can also copy the cells, select the destination range, and press Ctrl+V on Windows or Command+V on Mac. Check a few copied rows to confirm each formula refers to that row’s times and break.
- Review a shift that crosses midnight. For a shift starting at 10:00 PM and ending at 6:30 AM, with a 0.5-hour unpaid break, the formula in F2 returns 8 hours. That is because MOD calculates an elapsed span of 8.5 hours before the break is deducted. The date in column B should identify the shift’s start date consistently; if you need to report hours by calendar day, an overnight shift may need to be split across two rows.
- Choose whether you are calculating daily or weekly overtime. The G-column formula calculates overtime separately for each shift using a daily threshold. If your policy instead uses a weekly threshold only, first total the week’s worked hours. With that employee’s daily worked-hour results in F2:F6, enter this in H2:
=SUM(F2:F6). With the weekly total in H2 and a 40-hour weekly threshold, calculate weekly overtime in I2 with=MAX(0,H2-40). For example, 8.5 hours on each of five days totals 42.5 hours, so this weekly-only formula returns 2.5 overtime hours. - Keep daily and weekly rules distinct. Do not automatically add daily overtime and weekly overtime together. Some policies or applicable rules may require both calculations, while others define how overlapping hours are treated. Confirm the method that applies before using the results for payroll. If your workbook has multiple employees, use a separate weekly total for each employee rather than adding everyone’s hours together.
- Save and test the sheet before relying on it. Enter a normal daytime shift, a shift below the threshold, and an overnight shift. Compare each result with a hand calculation. Save the workbook, then protect or clearly mark formula columns if other people will enter data. This reduces the chance that a formula is accidentally overwritten.
Common Mistakes
- Entering a break in the wrong units. The formula above expects decimal hours in E2. Entering 30 for a 30-minute break subtracts 30 hours, not 30 minutes. Use 0.5, or adapt the formula if you prefer to enter break duration as a time value.
- Subtracting paid breaks. Only subtract breaks that should be excluded from worked time under the policy you are applying. A spreadsheet cannot decide whether a particular break counts as paid work.
- Using the wrong threshold. Eight hours per day and 40 hours per week are examples, not universal rules. A different schedule or policy may require another threshold or a more involved calculation.
- Mixing daily and weekly overtime. Daily overtime totals and a weekly threshold calculation are different methods. Adding both results without checking for overlap can overstate hours.
- Formatting decimal hours as clock time. A result of 1.5 decimal hours means 1 hour 30 minutes. If displayed as a clock time, it can look like 1:30 AM and confuse readers. Format results as Number.
- Rounding each shift too early. Rounding individual entries can change a weekly total. Keep the full calculated value and apply rounding only if the relevant payroll process requires it.
Tips
- Keep a dedicated input area for shift details and avoid typing over formula columns. Different fill colors for input and calculated cells can make the distinction clearer.
- Use data validation to reduce accidental entries. Select the time-entry cells and choose Data > Data Validation; set an appropriate time rule for start and end fields. Still review entries, since validation cannot determine whether a time is reasonable for a particular shift.
- Use a decimal break column consistently. If staff prefer to record a break as 0:30, enter it in a separate column formatted as a duration and change the calculation to convert that duration into hours. Do not mix decimal values and time values in the same break column.
- For weekly summaries, keep employee names and dates with each shift. A total that combines several employees or weeks will not be useful for checking one person’s overtime.
- Consider adding a separate notes column for approved schedule changes or unusual shifts. Documenting exceptions helps someone reviewing the sheet understand why an entry differs from the standard pattern.
Troubleshooting
The formula shows a decimal or a strange time. Select the result cells and set Home > Number Format to Number. If a start or end cell is showing as a number such as 0.375, format that cell as Time; Excel stores times as fractions of a day.
An overnight shift returns the wrong number. Check that the start and end entries are real time values and that the end time is on the following day. The MOD formula assumes a shift lasts less than 24 hours. If a shift can last 24 hours or more, enter full date-and-time values and use a calculation suited to those timestamps instead.
Excel displays the formula instead of calculating it. Make sure the cell is not formatted as Text. Change it to General or Number, then re-enter the formula. Also check that the entry begins with an equals sign and that Show Formulas is not turned on. On Windows, Ctrl+` toggles formula display; on Mac, the shortcut can vary by keyboard, so use Formulas > Show Formulas.
The result is negative or much too large. Check the break entry first: 0.5 represents half an hour, while 30 represents thirty decimal hours. Then confirm that the start and end times are in the correct columns and that the threshold is entered as hours in the overtime formula.
The total does not match a payroll report. Compare the time entries, unpaid breaks, rounding practice, pay period, and overtime rule being used. A spreadsheet that calculates one threshold per shift will not automatically account for every workweek rule, special schedule, or local requirement. Confirm the calculation method with the responsible payroll contact before using it for payment.
Frequently Asked Questions
How do I calculate overtime in Excel if my shift crosses midnight?
Use =MAX(0,MOD(D2-C2,1)*24-E2) for worked hours, assuming the start is in C2, end in D2, and unpaid break hours in E2. MOD treats an earlier end time as the next day for shifts shorter than 24 hours.
How do I calculate overtime after 40 hours in a week?
Total that employee’s worked hours for the week, then subtract 40 while limiting the result to zero. For example, with a weekly total in H2, use =MAX(0,H2-40). Replace 40 if the applicable weekly threshold differs.
Can I enter a 30-minute break as 0:30?
Not in the decimal-hours break column used here. Enter 0.5 there. If you want to record 0:30 as an Excel duration, use a separate time-formatted column and adjust the worked-hours formula to convert the duration to hours.
Why does 1.5 hours look like 1:30?
Excel is displaying the value as a time rather than a decimal number. Select the result cells and choose Number format. The underlying value is still 1.5 hours, which equals one hour and 30 minutes.
Can I use the same formula for payroll?
You can use it to calculate hours when its assumptions match the applicable policy, but it is not a complete payroll compliance tool. Confirm the correct overtime threshold, break treatment, rounding, and treatment of overnight or split shifts with your payroll team or a qualified professional.
Related
- How to Align an Image in an Excel Cell and Keep It in Place
- How to Align Cell Size in Excel for a Neater Sheet
- Excel Cell Alignment Shortcuts: A Step-by-Step Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Concatenate Names in Excel With a Space EasilyTo concatenate first and last names in Excel with a space, put the first name in A2 and…
- DAX SUMPRODUCT: Multiply and Sum Values in Power BIDAX does not have a built-in SUMPRODUCT function. To multiply values row by row and add the results,…
- Excel PV of Future Value: Formula and ExamplesTo find the present value of a future amount in Excel, use the PV function with no periodic…
- COUNTIF in Excel: Count Values Less Than a Set NumberTo count values below a number in Excel, use =COUNTIF(A2:A20,"<10"). Replace A2:A20 with your range and 10 with…
- Excel Data Validation INDIRECT: Dependent Drop-DownsYes. In Excel, use INDIRECT in a data-validation list to make a drop-down depend on a selection in…
- Excel Filter Button Shortcut: Keys, Menus, and FixesIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn filter buttons on or off for a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.