For a shift that starts in B2, ends in C2, and has an unpaid break entered as a duration in D2, use =MOD(C2-B2,1)-D2. For example, 9:00 AM to 5:30 PM with a 30-minute break returns 8:00; format the result as [h]:mm to display worked time clearly.
The basic hours-worked formula
Excel stores times as fractions of a day, so subtracting a start time from an end time gives an elapsed-time value. The formula’s MOD function handles a shift that crosses midnight, while subtracting D2 removes the break. This assumes each shift is shorter than 24 hours and the break is recorded as a duration, not as a clock time.
For the example, C2 is 5:30 PM, B2 is 9:00 AM, and D2 is 0:30. The elapsed shift is 8 hours 30 minutes; less the half-hour break, the result is 8 hours. The formula stores that result as a fraction of a day. A suitable cell format shows it as hours and minutes rather than a decimal fraction.
If no break is being deducted, use the same approach without D2. With the start in B2 and the end in C2, =MOD(C2-B2,1) returns the elapsed time. For a same-day shift from 9:00 AM to 5:30 PM, that is 8:30.
Set up the timesheet cells
A simple worksheet can use one row per shift, with headings that make each entry’s purpose obvious:
- A: Employee or shift date, if useful.
- B: Start time.
- C: End time.
- D: Unpaid break duration.
- E: Hours worked, where the formula goes.
Enter clock times in B and C, such as 9:00 AM and 5:30 PM. Enter a half-hour break in D as 0:30, or enter 30 minutes in a cell formatted as a time duration. The break should represent elapsed time, not the time of day the break began. For example, 12:00 PM describes a clock time, not a 12-hour break.
With start time in B2, end time in C2, break duration in D2, and the calculation in E2, enter =MOD(C2-B2,1)-D2 in E2. Using the sample entries above, E2 returns 8:00 when formatted as a duration. To apply the formula to more shifts, fill the formula down the column; Excel adjusts the row references for each shift.
Handle shifts that cross midnight
Ordinary subtraction can produce a negative value when the end time is earlier on the clock than the start time. For example, a shift beginning at 10:00 PM and ending at 6:00 AM crosses midnight. MOD in the basic formula wraps the time difference into the next day, so it calculates eight elapsed hours rather than a negative result.
With the start in B2, end in C2, and break duration in D2, =MOD(C2-B2,1)-D2 returns 7:30 for a 10:00 PM to 6:00 AM shift with a 30-minute break. That is seven and a half hours of worked time. Format the result as [h]:mm to see 7:30.
There is an important limit: if start and end are the same time, MOD treats the difference as zero, not a full 24-hour shift. Also, a time-only formula cannot tell whether an end time belongs to the same date or the following date in every possible case. For unusually long shifts or records where dates matter, enter full dates as well as times and use the date-aware method below.
Show the answer as decimal hours
Timesheet totals are often easier to compare or use in other calculations as decimal hours. In that format, 8 hours 30 minutes is 8.5, not 8.30: the minutes are a fraction of an hour, so 30 minutes equals 0.5 hour.
With start time in B2, end time in C2, and break duration in D2, use =(MOD(C2-B2,1)-D2)*24 to return decimal hours. For a 9:00 AM to 5:30 PM shift with a 30-minute break, the result is 8.5. Format the result cell as Number and choose the decimal places you want to display. Two decimal places would show 8.50.
Multiplying by 24 converts Excel’s fraction-of-a-day result into hours because a day has 24 hours. Do not multiply a result that you intend to display as hours and minutes: doing so changes its scale. You can keep one column in elapsed-time format for readability and another in decimal hours for a downstream calculation.
Use dates for more reliable calculations
When a shift can run overnight, storing its actual start and end dates removes ambiguity. In this layout, A2 contains the start date, B2 the start time, C2 the end date, D2 the end time, and E2 the unpaid break duration. For a shift starting at 10:00 PM on November 3 and ending at 6:00 AM on November 4, enter the two correct dates in A2 and C2.
With those cells arranged as described, enter =C2+D2-A2-B2-E2 in F2. If the shift is 10:00 PM to 6:00 AM and E2 is 0:30, the result is 7:30. Excel combines each date and time into a single date-time value, subtracts the start from the end, and then deducts the break. Format F2 as [h]:mm.
This setup is also useful for shifts that last longer than a day, since the dates establish the full elapsed interval. Make sure the end date is not accidentally left as the start date when the shift crosses midnight. A date-aware layout takes a little more data entry, but it makes the record easier to check and avoids relying on a formula to guess which day an end time belongs to.
Format and total hours correctly
For a duration result, select the result cells, press Ctrl+1 on Windows (Command+1 on Mac), choose the Number tab, select Custom, and enter [h]:mm as the format. The square brackets let the hour count continue past 24. A normal time format can cycle back to 0 after 24 hours, making a weekly total look wrong even when the underlying value is correct.
For example, a weekly total of 39 hours 15 minutes should display as 39:15. If you use a format that wraps after 24 hours, the same total may appear as 15:15. That is a display issue, not necessarily a faulty addition. Decimal-hour cells, by contrast, should use a Number format rather than a time format.
With daily worked durations in E2:E6 for five shifts, use =SUM(E2:E6) in E7 to total the week. If the shifts are 8:30, 8:00, 7:45, 8:30, and 6:30, the result is 39:15, or 39.25 decimal hours. Format E7 as [h]:mm for the first display. If the daily results are decimal hours instead, summing them produces 39.25 as a number.
Common errors and checks
A result that appears as a decimal such as 0.354167 usually means Excel is showing the underlying fraction of a day. Change the result cell’s number format to [h]:mm. A result that shows an unexpected time after a weekly sum may be using a 24-hour clock format; use the bracketed-hour format for totals.
Other useful checks include:
- Confirm that start and end entries are real Excel times, not text. If a value is left-aligned or does not respond to time formatting, re-enter it as a time.
- Check that breaks are entered as durations and are in the same row as the correct shift.
- Look for a negative result if the break is longer than the shift. That usually signals an incorrect entry.
- Verify the dates when using date-aware calculations, especially for shifts crossing midnight.
- Check that a cell does not contain an unintended date as well as a time; dates affect the elapsed-time calculation.
Excel calculates the time represented by the values you enter. It does not determine whether a break should be paid, how a workplace rounds time, or what rules apply to a particular timesheet. Those decisions depend on the applicable workplace policy and requirements. Keep the raw start, end, and break entries if you need a clear record of how a total was calculated.
Choosing between time and decimal results
Use hours and minutes when people need to read or check a shift duration: 8:30 is immediately recognizable as eight hours and 30 minutes. Use decimal hours when a report or another calculation expects ordinary numeric hours: 8.5 is the equivalent value. The two formats represent the same duration differently, so label the column clearly.
For a straightforward daily timesheet, the time-only formula is compact and handles typical overnight shifts. A date-aware formula is a better choice when the dates are known, shifts may be unusually long, or the sheet must preserve a precise timeline. Neither approach can correct mistaken input automatically, so consistent entry and a visible layout matter as much as the formula.
Frequently Asked Questions
What formula subtracts a lunch break in Excel?
With start time in B2, end time in C2, and unpaid break duration in D2, use =MOD(C2-B2,1)-D2. Enter a 30-minute break as 0:30. A 9:00 AM to 5:30 PM shift with that break returns 8:00.
How do I calculate hours worked overnight?
With a start time in B2 and end time in C2, =MOD(C2-B2,1) handles a shift crossing midnight. Subtract a break by adding the break duration in D2 and using =MOD(C2-B2,1)-D2. For shifts where dates matter, record both dates and times.
How do I make Excel show more than 24 hours?
Format the duration or total cell with the custom format [h]:mm. The brackets keep accumulating hours instead of restarting the display after 24.
How do I convert hours and minutes to decimal hours?
With a duration in E2, multiply it by 24. For example, =E2*24 returns 8.5 if E2 contains 8:30. Format the result as Number to display decimal hours.
Why is my hours-worked answer negative?
A basic subtraction may be negative when a shift ends after midnight, or a break may exceed the calculated shift. Use the MOD formula for a typical overnight shift, then check the entries. If dates are available, use the date-aware layout so the next-day end date is explicit.
Related
- Excel Desktop Keyboard Shortcuts: A Practical Guide
- Excel Dependent Drop-Down Lists in Microsoft 365
- Excel Dependent Drop-Down Lists With Spaces: Setup Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Calculate Age in Excel With a Birthday Date FormulaTo calculate someone’s age in completed years in Excel, enter their birthday as a real date in A2,…
- PROPER Function in Excel: Examples and TipsExcel’s PROPER function capitalizes the first letter of each word and changes the remaining letters to lowercase. For…
- Excel Filter All Rows Containing Text in a ColumnTo filter Excel rows that contain a word or phrase, open the filter arrow in the relevant column…
- Today’s Date in Excel: Shortcut and TODAY() FormulaIn Excel for Microsoft 365 on Windows, press Ctrl+; (Control plus semicolon) to insert today’s date as a…
- Excel Date Filter Multiple Criteria: Practical MethodsTo filter dates in Excel by multiple criteria, turn on AutoFilter, open the date column’s dropdown, and choose…
- Excel Formula to Reference a Sheet Name from a CellTo reference a worksheet named in a cell, use Excel’s INDIRECT function. If A1 contains the sheet name…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.