To calculate hours worked in Excel, subtract a shift’s start time from its end time and format the result as elapsed time.
Overnight shifts and totals over 24 hours need a small adjustment, and the examples below show when to use each formula.
Calculate hours worked for a same-day shift
Suppose a timesheet has a start time in cell B2 and an end time in C2.
Enter this formula in D2:
=C2-B2For a shift from 9:00 AM to 5:30 PM, the result is 8 hours and 30 minutes.
Excel stores times as fractions of a day, so subtracting one time from another returns a fraction that represents the elapsed time.
Format D2 to display the result as hours and minutes.
Select the cell, open Format Cells with Ctrl+1, choose Custom, and enter:
[h]:mmThe brackets around h tell Excel to show elapsed hours.
For a single shift, h:mm also works, but [h]:mm is safer for totals that may exceed 24 hours.
Calculate hours for an overnight shift
If a shift starts before midnight and ends the next day, a basic subtraction can produce a negative result.
For example, a shift from 10:00 PM to 6:00 AM crosses midnight.
Use MOD to wrap the difference into a 24-hour day:
=MOD(C2-B2,1)With B2 set to 10:00 PM and C2 set to 6:00 AM, the result is 8 hours.
Format the result as [h]:mm to show hours and minutes.
This formula assumes the shift lasts less than 24 hours and that the end time is on the same date or the following date.
If your timesheet records full dates as well as times, subtract the full date-time values instead:
=C2-B2For example, if B2 contains a start date and time and C2 contains the next day’s end date and time, the dates make the overnight interval explicit.
Format the result as [h]:mm.
Subtract an unpaid lunch break
If an employee takes an unpaid break, subtract its duration from the shift.
Suppose B2 contains the start, C2 the end, and D2 the break duration.
Enter:
=MOD(C2-B2,1)-D2For a 9:00 AM–5:30 PM shift with a 30-minute break entered in D2 as 0:30, the result is 8:00.
Enter break durations as time values, not as decimal hours: 0:30 represents 30 minutes, while 0.5 represents 12 hours when interpreted as a time fraction.
Alternatively, if you store the break in minutes as a number, such as 30, convert it to Excel time by dividing by the number of minutes in a day:
=MOD(C2-B2,1)-D2/1440Convert elapsed time to decimal hours
Payroll or billing systems often need hours as a decimal number.
If D2 contains an elapsed time, multiply it by 24:
=D2*24An 8-hour, 30-minute duration becomes 8.5 hours.
Format the result as Number and choose the decimal places you need.
To calculate decimal hours directly from start, end, and break cells, use:
=(MOD(C2-B2,1)-D2)*24Use decimal hours only when the destination expects them.
A value of 8.5 means eight and a half hours; it does not mean 8 hours and 50 minutes.
Total hours across a timesheet
If daily durations are in D2:D8, add them with:
=SUM(D2:D8)Format the total cell as [h]:mm.
Without brackets, Excel’s clock-style formats can roll over after 24 hours, making a 40-hour weekly total appear as 16:00.
The elapsed-time format displays 40:00 instead.
To show a weekly total as a decimal number of hours, use:
=SUM(D2:D8)*24Format that cell as a number.
If the daily formulas already return decimal hours, add those values directly and do not multiply by 24 again.
Handle blank rows and invalid time entries
For a timesheet where some rows have no start or end time yet, leave the duration blank until both entries are present:
=IF(OR(B2="",C2=""),"",MOD(C2-B2,1))This keeps unfinished rows from showing an unexpected result.
If your Excel version uses semicolons as formula separators because of regional settings, replace the commas with semicolons.
Make sure start and end entries are recognized as times.
A value stored as text may not subtract correctly.
You can test a cell by changing its number format to General: a valid Excel time appears as a decimal fraction.
Re-enter the value or convert it to a time if it remains text.
For shifts that could last 24 hours or more, a start and end time alone cannot identify the true duration.
Record both date and time, or add a separate indicator for the number of days, so the formula has enough information.
Excel and Google Sheets compatibility
The formulas in this article use standard functions available in current desktop versions of Excel, Excel for the web, and Google Sheets.
MOD, IF, OR, and SUM are supported in all three.
The main differences users encounter are regional formula separators and date or time display settings.
In Google Sheets, format elapsed durations with a duration format such as [h]:mm where available, or choose a duration display from the number-format options.
In either app, use a duration-style format for totals above 24 hours and multiply by 24 only when you need a decimal-hour value.