Excel Timesheet Formula With Breaks and Overnight Shifts

Formulas & functions · Updated November 18, 2026

With a shift start in B2, shift end in C2, break start in D2, and break end in E2, enter this in F2 to calculate paid time after the break: =MOD(C2-B2,1)-IF(COUNT(D2:E2)<2,0,MOD(E2-D2,1)). Format F2 as [h]:mm to show hours and minutes, including totals over 24 hours. The formula also handles overnight shifts; if the two break cells are empty, it subtracts no break.

How the timesheet formula works

Excel stores a time as a fraction of a 24-hour day. Subtracting a start time from an end time therefore gives a duration, but a shift that crosses midnight would otherwise produce a negative result. The MOD function wraps that difference into a positive part of a day. The formula then subtracts the break duration, calculated from the break end minus the break start.

For example, suppose B2 is 9:00 AM, C2 is 5:30 PM, D2 is 1:00 PM, and E2 is 1:30 PM. The shift lasts 8 hours 30 minutes; the break lasts 30 minutes; the net result in F2 is 8:00. If there is no break, leave both D2 and E2 empty. The COUNT test means Excel subtracts a break only when both cells contain numeric time values.

Enter genuine Excel times, not text that merely looks like a time. You can type values such as 9:00 AM and 5:30 PM, or use 24-hour entries such as 09:00 and 17:30. If Excel treats an entry as text, calculations may fail or behave unexpectedly. A quick check is to select the cell and inspect whether it behaves like a time when you change its number format.

Set up the columns and copy the formula

A practical daily layout keeps the information needed to audit a shift visible. Use one row per employee per workday, with headings in row 1 and the first shift in row 2:

  • A: Employee or staff ID
  • B: Shift start
  • C: Shift end
  • D: Break start
  • E: Break end
  • F: Net hours worked

Enter the formula in F2, then use the fill handle at the bottom-right corner of the selected cell to copy it down. Excel adjusts the row references for each copied row. In Microsoft 365 for Windows, you can also select the formula cell and drag or double-click its fill handle to fill adjacent rows when the neighboring data is continuous. Turning the range into a table with Insert > Table makes it easier to filter by employee or date, and Excel typically carries a calculated-column formula into new table rows.

Keep the original clock times as well as the calculated result. A total alone cannot show whether a deduction came from a real break or from a mistyped entry. If employees can take more than one unpaid break, either add a start-and-end pair for each break and subtract each duration, or use a separate total-break-duration column. Make the layout match the way the organization records time rather than squeezing several breaks into one ambiguous cell.

Use a break-duration column instead

If people record only the length of a break, a simpler layout may be more convenient. With shift start in B2, shift end in C2, and total break duration in D2, put this formula in E2: =MOD(C2-B2,1)-D2. Enter a 30-minute break as 0:30, not as the number 30; Excel interprets an ordinary number as a count of days in time calculations. Format D2 and E2 as [h]:mm if you want durations displayed consistently.

This approach works well when a time clock or paper record already supplies break length, or when several breaks are combined into one daily total. It does not preserve the time at which a break occurred, so it is less useful when a reviewer needs to verify the break against a schedule. If both the break start and end are recorded, the first formula calculates the length for you and leaves a clearer audit trail.

For an overnight example, suppose the shift begins at 10:00 PM and ends at 6:30 AM, with a break duration of 0:30. The shift spans 8 hours 30 minutes, and the result is 8:00. The same calculation works when the start and end are on the same day. The formula assumes each shift is shorter than 24 hours; for ordinary daily timesheets, that is usually the intended setup.

Handle overnight shifts and dated timestamps

With time-only entries, the MOD portion is what makes a shift such as 10:00 PM to 6:30 AM calculate as 8 hours 30 minutes rather than a negative time. The break calculation uses the same idea, so a break recorded from 1:00 AM to 1:30 AM is 30 minutes even if the overall shift started the previous evening. Enter both break times as clock times and make sure the break actually falls within the shift.

If you record a full date and time for each clock-in and clock-out, use those timestamps instead of time-only values when shifts might last 24 hours or longer. With the full start date and time in B2, full end date and time in C2, and break duration in D2, enter this in E2: =C2-B2-D2. The date portion distinguishes the next day from the starting day, so MOD is unnecessary and would discard whole days from the elapsed time. This method is also useful for shifts that cross several calendar dates.

For a dated shift, ensure the end timestamp contains the correct date. A clock-out of 6:30 AM on the following day must include that following date; entering only the time loses the distinction. Use a dedicated date-and-time entry process or a checked date column if staff enter times manually.

Format hours, minutes, and decimal totals

Excel stores the result as a fraction of a day. A result of eight hours is stored as one-third of a day, which is why the default General format may show a decimal that is not immediately readable. Select the result cells, press Ctrl+1 on Windows (Command+1 on Mac), and in Format Cells choose Number > Custom. Enter [h]:mm as the custom format. The square brackets make elapsed hours continue past 24; a plain h:mm display can wrap around and make a multi-day total appear misleading.

Use [h]:mm for a weekly total as well as for daily durations. For example, six shifts of 8 hours add up to 48:00. A format that wraps at midnight could show 0:00 instead, even though the underlying total is still correct. The format changes how a value is displayed, not the stored calculation.

Some payroll or reporting systems request decimal hours instead of hours and minutes. With net duration in F2, enter this in G2 to convert it to decimal hours rounded to two decimal places: =ROUND(F2*24,2). An 8-hour-15-minute result becomes 8.25 hours because 15 minutes is one quarter of an hour. An 8-hour-20-minute result is 8.33 after rounding. Keep the original duration column where possible; decimal rounding can make displayed values look slightly different from exact minutes.

Total a day or pay-period hours

To add daily durations, make sure the total uses a duration format too. With net hours in F2:F8, enter this in F9: =SUM(F2:F8). Format F9 as [h]:mm. If each of seven shifts is 8:00, the total is 56:00. A weekly total should sum the net-worked column, not the shift-end times or gross shift lengths, or unpaid breaks may be counted twice.

If the sheet has separate employee or date groups, filter the table before reviewing the displayed rows, but remember that ordinary SUM includes hidden rows. For a simple personal timesheet, a visible total may be enough. For a report that must recalculate based on filtered rows, use Excel’s SUBTOTAL function or build a PivotTable, and confirm that the chosen approach matches the reporting requirement. Those options are not necessary for a basic one-person weekly sheet.

Keep rounding decisions separate from time subtraction. Rounding each daily result to decimal hours before summing can produce a different total from summing exact minute durations and rounding once at the end. Follow the organization’s documented timekeeping and payroll rules; a spreadsheet formula should calculate the entries provided, not decide what rounding or compensation policy applies.

Common errors and ways to prevent them

A formula can be syntactically correct while the result is wrong because an entry is incomplete or incorrectly formatted. Check the source times as well as the output, especially when a result is negative, unexpectedly large, or zero. These checks prevent many routine timesheet mistakes:

  • One break cell is missing. The main formula subtracts a break only if both break cells contain times. It treats a single missing value as no complete break, so check that both entries are present when a break occurred.
  • A break is longer than the shift. The net result becomes negative. Correct the time entry rather than hiding the negative value with a formatting change.
  • A duration is entered as a number. For a break-length column, type a time such as 0:30. Typing 30 means 30 days, not 30 minutes.
  • A total appears to wrap or look wrong. Apply [h]:mm to duration cells. A display format can hide elapsed hours beyond a 24-hour clock cycle.
  • Times are stored as text. Re-enter them as valid times or correct the source data. Formatting text as a time does not necessarily convert it into a numeric time value.
  • A shift crosses midnight but uses an ordinary subtraction. Use the MOD-based formula for time-only entries, or use complete date-and-time timestamps and direct subtraction.

For repeatable entry, apply a time number format to clock-in and clock-out columns and use Data Validation where appropriate to restrict entries to times. Validation can reduce accidental entries, but it does not replace reviewing unusual shifts or incomplete break pairs. Keep a clear note explaining whether breaks are recorded as clock times or as a duration; mixing the two methods in one column is a common source of errors.

Choosing the right timesheet approach

Choose the simplest structure that preserves the information you need. Separate break start and end columns make it possible to review when the break occurred and calculate its length. A single break-duration column is compact and works for several breaks if their combined length is all that matters. Full date-and-time timestamps are the safer choice for multi-day work or records where the date of each punch matters.

Excel for Microsoft 365 on Windows uses the same formulas described here as Excel for Mac and Excel for the web. The main difference in the instructions above is the Format Cells shortcut: use Command+1 on Mac instead of Ctrl+1 on Windows. In Excel for the web, you can enter and fill the formulas as usual; number-format controls may be arranged differently from the desktop Format Cells dialog. The formula logic does not depend on a Windows-only feature.

Finally, distinguish elapsed time from payable time. A spreadsheet can subtract an entered break, but it cannot determine from the clock values alone whether a particular break should be unpaid, whether a rounding rule applies, or how a local policy treats exceptions. Confirm those rules with the employer’s timekeeping procedure and, where relevant, a qualified payroll professional.

Frequently Asked Questions

What is the formula for hours worked minus a lunch break in Excel?
With start in B2, end in C2, break start in D2, and break end in E2, use =MOD(C2-B2,1)-IF(COUNT(D2:E2)<2,0,MOD(E2-D2,1)). Format the result as [h]:mm.

How do I calculate an overnight shift in Excel?
For time-only start and end values, use MOD around the end-minus-start difference, as in the main formula. For dated timestamps, subtract the full start date and time from the full end date and time instead; format the result as [h]:mm.

How do I enter a 30-minute break in Excel?
If recording break start and end, enter both clock times, such as 1:00 PM and 1:30 PM. If using a break-duration column, enter 0:30 as a time duration. Do not enter the number 30, which Excel treats as 30 days in this calculation.

Why does my timesheet total show 0:00 after several days?
The total may be using a clock format that wraps after 24 hours. Apply the custom duration format [h]:mm to the total cell so it displays cumulative hours.

Can I use this formula when there is no break?
Yes. In the formula with separate break-start and break-end cells, leave both cells blank and the formula subtracts zero. If only one break cell is filled, the formula also subtracts zero, so check incomplete pairs rather than assuming they represent no break.

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.