How to Calculate Hours Worked in Excel

To calculate hours worked in Excel, enter a shift’s start time in B2, end time in C2, and unpaid break duration in D2, then use =MOD(C2-B2,1)-D2 in E2. Format E2 as [h]:mm to show the shift duration, including totals over 24 hours. This setup also handles a shift that ends after midnight; the steps below show how to enter times, calculate decimal hours, and check the results.

What You’ll Need

Excel for Microsoft 365 on Windows, with a blank workbook or a copy of your timesheet.
A consistent set of columns for each shift: employee or date (optional), start time, end time, break duration, and hours worked.
Times recorded in a format Excel recognizes, such as 8:30 AM or 17:00. For overnight shifts, record the start and end times on the same row.
A clear rule for whether breaks are paid. Enter only unpaid break time in the break column if your goal is to calculate paid hours.

Step-by-Step

Set up the columns. In row 1, add headers such as Date, Start, End, Unpaid Break, and Hours Worked. Put your first shift in row 2. For this guide, the start time goes in B2, end time in C2, unpaid break duration in D2, and calculated hours in E2. A break of 30 minutes can be entered as 0:30. You can omit the Date column if you only need a basic shift calculation, but adding dates helps distinguish repeated shifts and makes a timesheet easier to audit.
Enter the start, end, and break values as times. Type a value such as 9:00 AM or 09:00 in B2 and 5:30 PM or 17:30 in C2. Type 0:30 in D2 for a 30-minute unpaid break, or leave D2 blank when there is no unpaid break. Excel stores times as parts of a day, so entering a break as 30 rather than 0:30 would mean 30 days, not 30 minutes. Use one time convention throughout the sheet to make entries easier to review.
Enter the elapsed-time formula. With the start time in B2, end time in C2, and break duration in D2, enter =MOD(C2-B2,1)-D2 in E2. For a shift from 9:00 AM to 5:30 PM with a 30-minute break, the elapsed time is 8 hours: 8 hours 30 minutes on the clock minus 30 minutes unpaid. MOD wraps the subtraction across midnight, so a shift from 10:00 PM to 6:00 AM returns 8 hours before any break is deducted. If the break cell is empty, Excel treats it as zero in this calculation.
Format the result as elapsed time. Select E2, press Ctrl+1 on Windows, and choose Number > Custom in the Format Cells dialog. Enter [h]:mm in the Type box and select OK. The square brackets tell Excel to show accumulated hours rather than restart the hour count at 24; the colon and mm display minutes. On Excel for Mac, select the cell and use Command+1 to open Format Cells, then choose Custom and enter the same format. If you want to set the format for the whole results column, select E:E before opening the dialog.
Check the first answer before filling down. In the example above, E2 should display 8:00. If it displays a decimal such as 0.3333, the calculation may be right but the cell is using a General or Number format; apply the elapsed-time format. If it displays a clock time such as 8:00 AM, Excel is showing a time of day rather than an elapsed duration, so apply [h]:mm. Check the start, end, and break entries as well before copying the formula.
Copy the formula to other shifts. Select E2 and drag the small fill handle at the lower-right corner of the cell down through the remaining rows. You can also copy E2, select the destination cells, and paste. Excel adjusts the row references automatically, so the next row should refer to B3, C3, and D3. Keep the formula column separate from manual entries so you do not accidentally replace a calculation with a typed value.
Choose whether to show decimal hours. Payroll systems and reports sometimes request decimal hours rather than hours and minutes. With the same inputs in B2, C2, and D2, enter =((MOD(C2-B2,1)-D2)*24) in F2. For the 9:00 AM–5:30 PM shift with a 30-minute break, the result is 8. Format F2 as Number and choose a suitable number of decimal places. The result is 8.00 hours; a shift of 7 hours 30 minutes would be 7.50 hours, not 7.30, because the decimal part represents a fraction of an hour.
Total the shifts. With calculated durations in E2:E6, enter =SUM(E2:E6) in E7. Format E7 as [h]:mm so a total such as 42 hours 30 minutes displays as 42:30 instead of wrapping to 18:30. If you used decimal hours in F2:F6, total those decimal values with =SUM(F2:F6) in F7 and format F7 as Number. Keep a total for each employee or pay period separate if that is how your timesheet is organized.
Review the sheet for missing or unusual entries. Compare a few calculated shifts with the original times and confirm whether breaks are paid or unpaid. A blank end time is not a completed shift; avoid treating its calculated result as an approved zero-hour shift. You can use a separate status column or filter the time-entry columns to find incomplete rows before relying on the totals.

Common Mistakes

Using a clock-time format for totals: A normal time format resets after 24 hours. A weekly total of 42:30 may appear as 18:30. Apply the custom format [h]:mm to both duration cells and totals.

Entering a break as a plain number: A value of 30 in a time-formatted cell can represent 30 days. Enter a 30-minute break as 0:30. If your break data is deliberately recorded as a number of minutes, the formula must convert those minutes to a fraction of a day before subtracting them.

Subtracting end minus start without accounting for midnight: The direct calculation C2-B2 produces a negative value when the end time is earlier on the clock than the start time. MOD handles a single overnight shift with times alone. It does not infer a multi-day shift correctly if you enter only times and the shift lasts 24 hours or more.

Mixing paid and unpaid breaks: Subtracting every break reduces hours worked, even if a break is paid under your workplace’s rules. Decide which breaks should be deducted before building the formula, and verify the relevant company policy and applicable requirements rather than assuming all breaks have the same treatment.

Typing times as text: An entry with an apostrophe before it, an unexpected space, or an unrecognized time pattern may be stored as text. The subtraction can then return an error or an unexpected result. Re-enter the value in a recognized time format and check that the cell is not left-aligned as text when other times are right-aligned.

Tips

Apply time formatting to the input columns as well as the result. Select B:C, open Format Cells, and choose a time display that your team can read consistently. Formatting changes how a value appears, not the value itself, so it will not repair entries that were imported as text. For a longer timesheet, use clear headers, freeze the top row from View > Freeze Panes > Freeze Top Row, and keep one shift per row.

If you record start and end as full date-and-time values, you can use straightforward subtraction and avoid guessing which day the end belongs to. For example, a start entered as November 3 at 10:00 PM and an end entered as November 4 at 6:00 AM contain the date change explicitly. With those full date-and-time values in B2 and C2, and break duration in D2, use =C2-B2-D2 in E2. The result is 8:00 for a 30-minute break? No: the elapsed span is 8 hours, so after a 30-minute break the correct result is 7:30. Make sure the dates and times reflect the actual shift.

For an ordinary time-only shift, use the MOD formula rather than a direct subtraction, and decide how your sheet should handle an identical start and end time. MOD returns zero for equal times. If your business uses equal times to mean a full 24-hour shift, enter dates as well so Excel can distinguish a full day from no elapsed time.

Keep raw entries and calculated results in different columns. This makes it easier to correct a mistaken clock-in without losing the formula. If you export a decimal-hours total, retain enough decimal places for the intended reporting process and confirm whether rounding is required. Do not round individual entries unless your organization’s timekeeping rules call for it; rounding each shift can produce a different total from rounding once at the end.

When the schedule changes, check the formula references after inserting or moving columns. Excel often updates references automatically, but a quick test with a known shift can catch an accidental reference to the wrong break or time column. Save a copy before making broad changes to a workbook used for payroll records.

Troubleshooting

The cell shows ####. The column may be too narrow, or the formula may be returning a negative duration because a break is longer than the shift. Widen the column first. Then verify that the end time is later than the start time or that the MOD formula is being used, and check that the break value is entered as a duration such as 0:30 rather than 30.

The total is smaller than expected after 24 hours. The result is probably displayed with a standard time format. Select the total and set its custom format to [h]:mm. The formula may already be adding correctly; the display format is what causes the apparent wrap.

The formula displays as text instead of calculating. Check that the cell format is not Text and that the formula begins with an equals sign. Change the cell to General, press F2, then press Enter to make Excel evaluate the entry again. If the formula bar shows a leading apostrophe, remove it.

You see #VALUE! or another error. Check that B2 and C2 contain real Excel times rather than text, and that D2 is blank or a valid duration. Re-enter a problem value using a format such as 9:00 AM or 17:00. If the data came from a downloaded report, a text-to-columns conversion or re-entry may be needed to turn text into times.

An overnight result is wrong. Confirm that you used =MOD(C2-B2,1)-D2 and that the shift is shorter than 24 hours. If the shift spans multiple days, include actual dates with the times and subtract the full date-time values instead. Also check that the end time is not accidentally on the wrong date.

Frequently Asked Questions

How do I calculate hours worked in Excel without a break? With a start time in B2 and end time in C2, use =MOD(C2-B2,1) in the result cell and format the result as [h]:mm. This handles a shift crossing midnight, provided it lasts less than 24 hours.

How do I calculate hours as a decimal? With start time in B2, end time in C2, and break duration in D2, use =((MOD(C2-B2,1)-D2)*24). Format the result as Number. For example, 7 hours 30 minutes becomes 7.5 hours.

Can Excel calculate a shift that ends the next day? Yes. For time-only entries, the MOD formula treats an end time earlier than the start time as occurring after midnight. If the shift can last 24 hours or more, enter the calendar date with each time and subtract the full date-time values instead.

Why does my total show 18:30 instead of 42:30? The total is probably formatted as a time of day, which wraps at 24 hours. Set the total cell to the custom number format [h]:mm to display accumulated hours.

How do I enter a 45-minute break? Enter 0:45 in the break cell, which Excel reads as 45 minutes. Do not enter 45 as a plain number unless your sheet is designed to store breaks as minutes and the formula converts minutes appropriately.

Does this formula calculate overtime pay? No. It calculates elapsed hours after subtracting the break value. Overtime thresholds, rates, rounding, and pay treatment depend on the applicable workplace rules and agreements, so confirm those rules before using a spreadsheet to prepare payroll.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.