To calculate hours worked in Excel, subtract the start time from the end time and multiply by 24: =(B2-A2)*24. With a start time in A2 and an end time in B2, this returns decimal hours; for an 8:30 a.m. to 5:00 p.m. shift, the result is 8.5 hours.
How the basic hours formula works
Excel stores times as fractions of a day: one hour is 1/24 of a day. Subtracting one time from another therefore gives a fraction, not a number of hours. Multiplying the difference by 24 converts it into hours. For example, a difference of 0.35417 days is 8.5 hours.
Assuming A2 contains 8:30 AM and B2 contains 5:00 PM, enter this formula in C2 to return 8.5:
=(B2-A2)*24
Enter the times as actual Excel time values, such as 8:30 AM or 5:00 PM, rather than text that only looks like a time. Usually, typing a recognizable time into a cell is enough for Excel to store it as a time value. If the result appears as a time of day instead of a decimal, change the result cell’s number format to General or Number.
This calculation counts the full elapsed time between the two entries. It does not automatically subtract lunch, unpaid breaks, or time away from work. If the shift begins and ends on the same date and does not cross midnight, the basic subtraction is the simplest option.
Subtract an unpaid break
Put the start time in A2, the end time in B2, and the unpaid break duration in C2. Enter the break as a duration, such as 0:30 for 30 minutes. With a 9:00 AM start, 5:30 PM end, and 30-minute break, use this formula in D2; it returns 8 hours:
=(B2-A2-C2)*24
The break cell must represent elapsed time, not a time of day. A value of 0:30 means half an hour; a value such as 12:30 PM is a clock time and is not the right entry for a 30-minute break. You can enter breaks in hours and minutes, including 1:00 for one hour. If you record break lengths as decimal hours instead, such as 0.5 for 30 minutes, use a formula that subtracts the decimal directly:
With A2 as the start time, B2 as the end time, and C2 as the unpaid break in decimal hours, this formula returns 8 for a 9:00 AM–5:30 PM shift with a 0.5-hour break:
=(B2-A2)*24-C2
Use one break-entry convention consistently across the sheet. Mixing clock-time entries and decimal-hour entries can produce incorrect totals that may not be obvious at a glance.
Calculate shifts that cross midnight
A shift from 10:00 PM to 6:00 AM ends on the following day. If the cells contain only clock times, ordinary subtraction produces a negative result because Excel treats the end time as earlier than the start time. The MOD function wraps the difference through midnight.
With A2 containing 10:00 PM and B2 containing 6:00 AM, use this formula to return 8 hours:
=MOD(B2-A2,1)*24
MOD returns the remainder after dividing the time difference by one day, so the result is a positive part of a day. Multiplying by 24 converts it to hours. To subtract a break entered as a time duration in C2, use:
With A2 as the start, B2 as the end, and C2 as an unpaid break duration, this formula returns 7.5 for a 10:00 PM–6:00 AM shift with a 30-minute break:
=(MOD(B2-A2,1)-C2)*24
There is an important limitation: when both cells contain times only, MOD treats identical start and end times as a full 24-hour span. If a shift could genuinely start and finish at the same clock time, record the dates as well, or add a separate rule for that situation. Do not assume identical times mean either zero hours or 24 hours without checking what the entry represents.
Use dates for overnight and multi-day shifts
For schedules that span midnight, include the date with each time. Excel stores a date and time together as a serial value, so subtracting the earlier date-time from the later one gives the true elapsed duration across midnight without needing MOD.
With A2 containing the start date and time, B2 containing the end date and time, and C2 containing an unpaid break duration, this formula returns paid hours. For example, a shift from 10:00 PM on November 3 to 6:00 AM on November 4, minus a 30-minute break, returns 7.5:
=(B2-A2-C2)*24
Use full date-time entries when shifts might last longer than a day or when a clock-only formula would be ambiguous. A date-time approach also makes it easier to review which calendar day a shift belongs to. Check that the end date is later than the start date; a mistyped date can make the calculated duration negative or unexpectedly large.
Add daily hours into a weekly total
For a weekly timesheet, calculate each day’s paid hours in one column, then sum those daily results. This keeps the daily calculations visible and makes it easier to spot a missing or unusual entry. For instance, suppose daily decimal-hour totals are in D2:D6 and represent 8, 8.5, 7.5, 8, and 8. The total is 40 hours.
With the five daily totals in D2:D6, enter this formula in D7 to return 40:
=SUM(D2:D6)
If the daily cells contain durations as Excel time values rather than decimal hours, their sum may exceed 24 hours. Excel can still store that total, but a standard time format may display it as a clock time that wraps back to zero after 24 hours. Format the total as a cumulative duration using the custom format [h]:mm. The square brackets make the hours accumulate instead of resetting at each day.
For example, with daily durations in D2:D6 totaling 40 hours, the SUM formula is the same. The result cell should use the [h]:mm format to display 40:00. To apply a custom format in Excel for Microsoft 365 on Windows, select the cell, press Ctrl+1, choose Number > Custom, type [h]:mm in the Type box, and select OK. In Excel for Mac, select the cell and use Command+1 to open Format Cells; the custom number-format option is also available there.
Choose decimal hours or hours and minutes
Decimal hours are convenient for calculations and many payroll or reporting systems. The value 8.5 means eight and a half hours, while 8.25 means eight hours and 15 minutes. It does not mean 8 hours and 25 minutes: the decimal part is a fraction of an hour, so 0.25 × 60 = 15 minutes.
A cumulative hours-and-minutes display is often easier to read on a timesheet. If A2 is the start time and B2 is the end time, this formula returns an elapsed-time value rather than decimal hours:
=B2-A2
Format its result as [h]:mm to display a total such as 8:30. If you leave the result in General format, Excel may show a decimal fraction of a day; if you use a standard time format, totals above 24 hours may wrap. Choose a format that matches how the value will be read and used, and remember that number formatting changes how a value looks, not the underlying calculation.
Common formula problems and checks
- The result looks like a time of day. The calculation may be correct, but the result cell is formatted as Time. Change it to Number or General for decimal hours, or use
[h]:mmfor a cumulative duration. - The result is negative for an overnight shift. With clock times only, use MOD as shown above, or enter full start and end date-times and subtract them.
- The answer is much too large or too small. Check whether a break cell contains a duration or a clock time, whether the formula multiplies by 24 when decimal hours are wanted, and whether the time entries are real Excel values rather than text.
- The weekly total appears to reset. Apply the custom
[h]:mmformat to a total stored as a time value. A normal hours-and-minutes display can wrap after 24 hours. - A blank row shows an unexpected result. A formula that references an empty start or end cell can still calculate a value. Leave the result blank until both inputs exist by using an IF check.
For example, with the start time in A2 and end time in B2, this formula leaves the result blank unless both cells contain entries; once they do, it returns decimal hours for a same-day shift:
=IF(OR(A2="",B2=""),"",(B2-A2)*24)
This version assumes shifts do not cross midnight. For clock-only overnight entries, replace the calculation with a MOD-based expression. You can also check entries with a few known cases: a one-hour shift should return 1, a half-hour break should reduce the paid total by 0.5, and an 8-hour shift should display 8 or 8:00 depending on the format.
Which method should you use?
Choose the formula based on the data you record and the output you need, rather than trying to force every timesheet into one calculation.
- For same-day shifts and decimal-hour output, use
=(B2-A2)*24. - For same-day shifts with a break stored as a time duration, subtract the break before multiplying by 24.
- For clock-only overnight shifts, use MOD to wrap the difference through midnight.
- For shifts with dates, multiple days, or potentially ambiguous start and end times, enter full date-time values and subtract them.
- For totals meant to display as hours and minutes, retain a time duration and apply
[h]:mm.
When choosing between decimal hours and an hours-and-minutes display, consider the next step. Decimal hours are easier to add to other decimal values, while duration formatting can make a shift or weekly total more intuitive to inspect. If a separate payroll system requires a particular format or rounding rule, follow its specifications; an Excel duration calculation does not determine the rules for rounding or compensating work time.
Frequently Asked Questions
How do I calculate hours worked in Excel with a lunch break?
Put the start and end times in A2 and B2 and the lunch duration in C2. If the break is entered as a time duration, use =(B2-A2-C2)*24 for decimal hours. Subtract only the portion of the break that should be excluded from the total.
How do I calculate hours worked overnight in Excel?
For clock-only start and end times in A2 and B2, use =MOD(B2-A2,1)*24. If the cells include both dates and times, subtract the start date-time from the end date-time and multiply by 24 for decimal hours.
Why does Excel show 0.5 instead of 12 hours?
Excel represents 12 hours as half of a day, or 0.5. Multiply the time difference by 24 to display decimal hours, or format the result as a duration such as [h]:mm.
How do I total more than 24 hours?
Use SUM on the duration cells. Then format the total as [h]:mm so Excel displays cumulative hours instead of wrapping after a day. If the daily values are already decimal hours, SUM returns a decimal-hour total directly.
Does the basic formula subtract breaks automatically?
No. Subtract the break explicitly by including its duration in the calculation. Check whether the break is paid or unpaid according to the timesheet rules you need to apply; Excel cannot infer that from the start and end times.
Related
- Excel File Too Large for No Reason? Find the Hidden Data
- Excel File Large for No Reason? Causes and Fixes to Try
- Excel Essential Keyboard Shortcuts for Everyday Work
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Filter Shortcut: How to Turn Filters On and OffIn Excel for Microsoft 365 on Windows, press Ctrl+Shift+L to turn AutoFilter on or off for a data…
- Sales Comparison Formula in Excel: Examples & TipsFor a sales comparison in Excel, calculate the change as new sales minus old sales, then divide that…
- Filter Duplicates in Excel: Keep One or Remove All?To remove duplicate entries in Excel for Microsoft 365 on Windows, select your data and choose Data >…
- Excel Goal Seek Formula: How to Loop ItExcel does not have a worksheet function called LOOP or GOALSEEK: Goal Seek is a built-in What-If Analysis…
- Excel Day and Date Formulas: Show Days, Dates, and NamesIn Excel, use =DAY(A2) to return the day of the month from a date in A2, or =TEXT(A2,"dddd")…
- Cash Flow Formula in Excel: A Step-by-Step TutorialIn Excel, net cash flow for a period is cash inflows minus cash outflows. If inflows are in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.