Excel Timesheet Formula Minus Lunch: Simple Examples

Formulas & functions · Updated November 18, 2026

To subtract an unpaid lunch entered as minutes, put the shift start in B2, the end in C2, and lunch minutes in D2; use =MOD(C2-B2,1)-D2/1440. Format the result as [h]:mm to show the net hours worked, including totals above 24 hours.

How the lunch deduction works

Excel stores a time as a fraction of a 24-hour day: one hour is 1/24, and one minute is 1/1440. Subtracting a start time from an end time therefore gives a fraction of a day, not a number of hours. Dividing lunch minutes by 1440 converts them to the same kind of value before Excel subtracts them.

For example, suppose an employee starts at 8:30 AM, finishes at 5:00 PM, and takes a 30-minute unpaid lunch. The elapsed shift is 8 hours 30 minutes. The deduction is 30 minutes, leaving 8 hours. With start, end, and lunch recorded in B2, C2, and D2 as described above, the formula returns 8:00. The result is a duration, not a clock time such as 8:00 AM.

The MOD function makes the formula work when a shift crosses midnight, as long as the shift is less than 24 hours. For instance, a 10:00 PM start and a 6:30 AM finish is 8 hours 30 minutes elapsed; after a 30-minute deduction, the net duration is 8:00. If every shift includes the same unpaid break, you can enter the minutes in each row or refer to a fixed cell containing that standard deduction.

Set up a simple timesheet

A clear layout makes formulas easier to check and copy. Use one row per employee per workday, with headings that identify whether lunch is recorded as minutes or as start and end times. For example:

  • Column A: employee or date
  • Column B: shift start
  • Column C: shift end
  • Column D: unpaid lunch, in minutes
  • Column E: net hours worked

Enter actual Excel times in B and C, such as 8:30 AM and 5:00 PM, rather than typing labels such as eight-thirty. In D, enter a number such as 30, not the time value 0:30, if you are using the minutes-based formula. Put the formula in E2, then use the fill handle to copy it down the column. If you turn the range into an Excel table with Insert > Table, a formula entered in a table column normally fills down for the other rows automatically.

For a consistent lunch deduction, put the number of minutes in a separate cell, such as G1, and use an absolute reference so it stays fixed when you copy the formula. With start times in B2, end times in C2, and the standard lunch minutes in G1, use =MOD(C2-B2,1)-$G$1/1440. The dollar signs lock the reference to G1. If the deduction changes for a particular day, a row-by-row lunch column is more flexible.

Format the result as elapsed time

Excel may initially display a calculated duration as a clock time or a decimal-looking value. Select the result cells, press Ctrl+1 on Windows to open Format Cells, choose the Number tab, select Custom, and enter [h]:mm in the Type box. The square brackets tell Excel to show total elapsed hours instead of wrapping the display back to zero after 24 hours. On a Mac, Command+1 opens Format Cells; the available dialog layout can vary slightly by Excel version.

For example, summing five daily results of 8:00 gives 40:00 with the elapsed-time format. A regular h:mm format can show that total as 16:00 because the clock-style hour display wraps after a day. The underlying value may still be correct, but the display is misleading for a timesheet total. Use [h]:mm for both daily duration results and weekly totals when you want accumulated hours and minutes.

Some timesheet reports need decimal hours rather than hours and minutes. With start in B2, end in C2, and lunch minutes in D2, use =(MOD(C2-B2,1)-D2/1440)*24. For the 8:30 AM to 5:00 PM example with 30 minutes unpaid, this returns 8. To show two decimal places, format the result as Number and choose two decimal places; the example then displays 8.00. Decimal display is useful for some reports, but the rounding or precision rules should match the requirements of the person or system receiving the timesheet.

Subtract lunch recorded as start and end times

If workers take different-length breaks, recording when lunch begins and ends can be more informative than entering a deduction in minutes. Suppose the shift starts in B2 and ends in C2, lunch starts in D2, and lunch ends in E2. With all four times on the same date, use =(C2-B2)-(E2-D2). If the shift and break are 8:30 AM–5:00 PM and 12:30 PM–1:00 PM, respectively, the result is 8:00.

To account for a shift that crosses midnight, use the MOD version. With the same cell layout, where the lunch start and end are within the overnight shift, use =MOD(C2-B2,1)-MOD(E2-D2,1). For a 10:00 PM–6:30 AM shift with a 2:00 AM–2:30 AM break, this returns 8:00. This approach assumes the break itself is shorter than 24 hours and that the entered times represent the intended break within that shift.

When dates are included in the cells as well as times, simple subtraction of complete date-time values is usually preferable because it preserves the actual number of days. For example, a shift that spans more than one midnight needs its dates recorded; a time-only formula using MOD intentionally treats the difference as a duration within a single 24-hour cycle.

Calculate a weekly total

Once each day’s net duration is calculated, add the daily results. If daily net durations are in E2:E6, use =SUM(E2:E6) in the weekly total cell. Format that cell as [h]:mm to display the accumulated hours correctly. If the daily results are decimal hours instead, SUM adds the decimal values; do not multiply that total by 24 again.

Keep the calculation columns separate when possible: start, end, lunch, daily net, and weekly total. This makes it easier to spot an unexpected result and explain how it was calculated. You can also label the lunch column clearly as unpaid minutes or break times. Mixing those two input methods in one column is likely to lead to formula errors or confusing entries.

Common errors and how to fix them

  • Lunch is deducted twice. If the recorded end time is already adjusted to exclude lunch, do not subtract lunch again. The formula assumes the start and end span the full shift, including the break.
  • The result appears as a time of day. Apply the custom format [h]:mm to show an elapsed duration. For weekly totals, this matters especially once the total exceeds 24 hours.
  • The formula gives a negative or unexpected result. Confirm that start and end cells contain real time values and that lunch minutes are numeric. Check whether the end is earlier than the start because the shift crosses midnight; MOD handles a time-only overnight shift under 24 hours.
  • A lunch deduction is stored in the wrong units. The minutes-based formula expects a number such as 30. If D2 contains an Excel time value such as 0:30 instead, use a formula that subtracts the time value directly rather than dividing it by 1440.
  • There are blank or incomplete rows. A calculation may show a result before a shift is fully entered. Consider leaving the result blank until both start and end have been entered. With start in B2, end in C2, and lunch minutes in D2, use =IF(OR(B2="",C2=""),"",MOD(C2-B2,1)-D2/1440). This returns a blank until both time cells are filled; it still expects D2 to contain a numeric lunch deduction.
  • The displayed total seems rounded. Excel stores time values with more precision than the displayed format may show. Changing the number format changes what is visible, not the underlying calculation. Avoid rounding each day unless the timesheet rules specifically require it.

Check the result and choose the right method

Use a small test row before copying a formula through a full timesheet. Check a regular daytime shift with a known break, then check an overnight shift if those occur. For the basic minutes method, a 9:00 AM start, 5:30 PM finish, and 30-minute lunch should produce 8:00. If it does not, verify the input values and number format before changing the formula.

Choose the minutes method when the deduction is a known number, such as a standard 30-minute unpaid lunch. It is compact and easy to review. Choose separate lunch start and end columns when break lengths vary or when the actual break times need to be visible in the record. If shifts can last more than 24 hours, include full dates and calculate with complete date-time values rather than relying on a time-only MOD calculation.

A formula only calculates from the values entered; it does not determine whether a break should be paid or how a workplace should record time. Those details depend on the applicable workplace rules and the system’s requirements. Use the appropriate policy for your situation, and make sure the workbook’s labels accurately describe the time being deducted.

Frequently Asked Questions

What is the simplest Excel formula to subtract a 30-minute lunch?
With start time in B2 and end time in C2, use =C2-B2-TIME(0,30,0) for a same-day shift. Format the result as [h]:mm. For a variable lunch entered as minutes in D2, use =MOD(C2-B2,1)-D2/1440.

Should lunch be entered as 30 or 0:30?
Either can work, but the formula must match the entry. Enter 30 for the minutes-based formula that divides D2 by 1440. If you enter 0:30 as an Excel time value, subtract that value directly from the elapsed shift instead.

How do I subtract lunch from an overnight shift?
For start in B2, end in C2, and unpaid lunch minutes in D2, use =MOD(C2-B2,1)-D2/1440. MOD treats an end time earlier than the start as occurring after midnight, for a time-only shift shorter than 24 hours.

Why does my Excel total show 16:00 instead of 40:00?
The cell is probably using a clock-style format that wraps after 24 hours. Select the total, open Format Cells with Ctrl+1 on Windows or Command+1 on Mac, and set a custom format of [h]:mm.

How can I show timesheet hours as a decimal?
Multiply the net time fraction by 24. With start in B2, end in C2, and lunch minutes in D2, use =(MOD(C2-B2,1)-D2/1440)*24. Format the result as a number and choose the decimal places your report requires.

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.