Excel Timesheet Formula: Total Hours, Breaks, and Shifts

For a timesheet with a start time in B2, an end time in C2, and an unpaid break duration in D2, enter =MOD(C2-B2,1)-D2 in E2. Format E2 as [h]:mm to show the total as hours and minutes, including totals greater than 24 hours.

How an Excel timesheet total works

Excel stores times as fractions of a 24-hour day: noon is one-half of a day, and an hour is one twenty-fourth. Subtracting a start time from an end time therefore produces a duration, but the underlying result is a fraction rather than a number of hours. A cell’s number format determines how that value appears.

The formula above calculates elapsed time and subtracts the break. The MOD function makes the elapsed-time part work when a shift crosses midnight, such as 10:00 PM to 6:00 AM. The break cell should contain a duration, not a clock time: enter 30 minutes as 0:30, for example. If there is no unpaid break, leave D2 blank or enter 0:00.

This setup assumes that each shift lasts less than 24 hours and that B2, C2, and D2 contain real Excel time values rather than text that merely looks like a time. The formula returns an Excel duration, so formatting it as a clock time can produce a misleading display. The elapsed hours themselves are what the timesheet needs.

Set up the timesheet columns

A simple daily layout keeps the entries and calculated results easy to check. Put headers in row 1, then enter one work period per row:

Column A: work date
Column B: start time
Column C: end time
Column D: unpaid break duration
Column E: total hours worked

Enter times consistently, such as 8:30 AM and 5:15 PM. Excel normally recognizes these as time values when entered in a time format. For a duration, enter a value such as 0:45 for a 45-minute break. If a break is paid and your timesheet is meant to report paid time, do not subtract it; whether a break is paid depends on the applicable workplace rules and policy.

To apply the calculation to more days, copy the formula from E2 down the column using the fill handle at the lower-right corner of the selected cell. Excel adjusts the row references as it fills. Keep one shift per row when possible: it makes unusual entries, missing punches, and weekly totals easier to review than combining several shifts in one cell.

Calculate and check a daily total

With start time in B2, end time in C2, and an unpaid break of 45 minutes in D2, enter the following in E2:

=MOD(C2-B2,1)-D2

For a shift from 8:30 AM to 5:15 PM, the elapsed time is 8 hours 45 minutes. Subtracting a 45-minute break leaves 8 hours, so the correctly formatted result is 8:00. This example is a useful check that the break is entered as a duration and is being deducted once.

For an incomplete row, you can keep the result blank until both the start and end times have been entered. With start and end times in B2 and C2 and the break in D2, use this version in E2:

=IF(OR(B2="",C2=""),"",MOD(C2-B2,1)-D2)

The formula checks whether either punch cell is empty. A blank break is treated as zero in the subtraction. This avoids displaying a calculated duration on rows that are not yet ready, though it does not validate whether an entered time is correct.

Format hours so they do not reset at 24

For a single shift shorter than a day, a time display may look fine. The common problem appears when you add several days: a standard time format cycles back to zero after 24 hours. For example, a 40-hour weekly total might display as 16:00 if the cell uses a clock-style format. The underlying sum can still be correct; the display format is the issue.

Use the elapsed-hours format [h]:mm. On Excel for Microsoft 365 for Windows, select the result cells, press Ctrl+1, choose the Number tab, select Custom, type [h]:mm in the Type box, and choose OK. The square brackets tell Excel to show cumulative hours instead of wrapping after 24. On a Mac, Command+1 opens Format Cells; the same custom format is appropriate.

Use [h]:mm for duration results and totals, not h:mm AM/PM, which is intended to show a time of day. If the cell shows a decimal such as 0.3333, the calculation may be correct but the cell is formatted as General or Number. Apply the duration format to display hours and minutes.

Handle shifts that cross midnight

When a shift starts before midnight and ends after it, directly subtracting the end time from the start time can produce a negative result. The MOD version in the main formula treats the end time as belonging to the next day when the end clock time is earlier than the start clock time. For example, 10:00 PM to 6:30 AM is 8 hours 30 minutes; after a 30-minute unpaid break, the result is 8 hours.

With the start time in B2, end time in C2, and break duration in D2, the overnight calculation is:

=MOD(C2-B2,1)-D2

There is an important limitation: because this version uses clock times without dates, it assumes the shift is under 24 hours. If the start and end cells contain actual date-and-time values, subtraction can use both dates and times instead. With the start date and time in B2, the end date and time in C2, and the break duration in D2, use:

=C2-B2-D2

For example, a start recorded on Monday at 10:00 PM and an end recorded on Tuesday at 6:30 AM produces 8 hours 30 minutes before breaks. Use [h]:mm for the result in either setup. Including dates is also a clearer option if a schedule can contain shifts longer than 24 hours or if records need to identify the actual work date.

Show a decimal number of hours

Some payroll, billing, or reporting systems request decimal hours instead of hours and minutes. A duration of 8 hours 30 minutes is 8.5 decimal hours, not 8.30; the minutes are a fraction of an hour. To convert the calculated duration to decimal hours, multiply the duration by 24.

With start time in B2, end time in C2, and break duration in D2, use this formula in E2:

=(MOD(C2-B2,1)-D2)*24

For an 8:30 AM to 5:15 PM shift with a 45-minute break, it returns 8. Format E2 as Number, perhaps with two decimal places, to display 8.00. For a shift with 8 hours 30 minutes after breaks, the result is 8.5. Keep decimal-hour results separate from duration-formatted results so users do not mistake a decimal for an hours-and-minutes display.

Sum daily hours into a weekly total

Once each daily result is in column E, a weekly total can be calculated in the row below the final shift. With daily durations in E2:E8, enter this in E9:

=SUM(E2:E8)

Apply [h]:mm to E9 as well. If the daily results add up to 40 hours, the total will display as 40:00. If the total cell uses a standard time format, it may appear as 16:00, because 40 hours is one full day plus 16 hours. Changing the format does not alter the arithmetic; it makes the accumulated duration visible.

For a longer timesheet, adjust the SUM range to cover the intended rows, and check that it includes every workday but not the total cell itself. If a sheet has multiple employees, make a separate total for each person or use a clearly defined range for each employee. A total is only as reliable as the entries it includes, so review blank rows and duplicate shifts before relying on it.

Common formula and display problems

The result is a decimal or fraction. The calculation may be correct, but the cell is not formatted as a duration. Apply [h]:mm for hours and minutes, or Number for decimal hours.
The total seems too small after several days. Check whether the cell uses a clock format that wraps at 24 hours. Use the bracketed-hours format for daily or weekly duration sums.
An overnight shift is negative or wrong. For time-only entries, use the MOD calculation and confirm the shift is under 24 hours. If the entries include dates, use actual date-and-time values and subtract them directly.
The formula gives an error or ignores a punch. Check that the cells contain recognizable Excel times, not text imported from another system. Re-entering the value or converting the source data to times may be necessary.
The result is negative. Check for a break longer than the shift, a mistyped time, or an incorrectly paired start and end. MOD can interpret an earlier end time as the next day, so it will not identify every mistaken entry by itself.

Also check how breaks are recorded. A break entered as 30 is not the same as a time duration of 30 minutes and may be treated as 30 days or otherwise produce an unintended result. Enter a duration such as 0:30, and make the column heading clear that the value is a break length rather than a clock time.

Choose the right calculation for the sheet

For a straightforward timesheet with one shift per day and shifts shorter than 24 hours, use time-only entries with MOD. It handles overnight clock times without requiring an end-date column. For schedules that need accurate dates, may exceed 24 hours, or span more complex date boundaries, record full start and end date-times and subtract them directly. For a report that needs decimal hours, multiply the duration result by 24 and format it as a number.

Before using a timesheet for payroll or invoicing, decide what the total is meant to represent: elapsed time, paid time, or another defined measure. The spreadsheet can perform the arithmetic, but it cannot determine which breaks should be deducted, how rounding should work, or what employment rules apply. Follow the relevant policy and verify any required timekeeping method rather than assuming the formula establishes those rules.

Frequently Asked Questions

What is the formula to add up hours in Excel? For daily durations in E2:E8, use =SUM(E2:E8) in the total cell, then format that cell as [h]:mm. The bracketed format displays totals above 24 hours without wrapping.

How do I subtract a lunch break from total hours? Put the unpaid break length in a cell as a duration, such as 0:30 for 30 minutes. With start in B2, end in C2, and break in D2, use =MOD(C2-B2,1)-D2 and format the result as [h]:mm.

How do I calculate an overnight shift in Excel? If you have clock times only and the shift is under 24 hours, use =MOD(C2-B2,1) for start in B2 and end in C2, then subtract any unpaid break. If each entry includes a date and time, subtract the start date-time from the end date-time instead.

Why does my 40-hour total show 16:00? The total cell is likely using a time-of-day format that resets after 24 hours. Change its custom number format to [h]:mm so Excel displays accumulated hours.

How do I show total hours as a decimal? Multiply the duration by 24. With start in B2, end in C2, and break in D2, use =(MOD(C2-B2,1)-D2)*24 and format the result as Number. Eight hours and 30 minutes will display as 8.5, not 8.30.

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.