Excel Time Formulas for Calculating Hours Worked

To calculate hours worked in Excel, subtract the start time from the end time and multiply by 24; for a shift that may cross midnight, use =MOD(C2-B2,1)*24. This example assumes the start time is in B2 and the end time is in C2, entered as Excel times; a shift from 9:00 AM to 5:30 PM returns 8.5 hours.

How the basic hours-worked formula works

Excel stores times as fractions of a 24-hour day. For example, six hours is one quarter of a day. Subtracting an earlier time from a later time therefore produces a fraction, not a number of hours. Multiplying the difference by 24 converts that fraction to decimal hours, which are usually convenient for totals and payroll calculations.

For a same-day shift, the simplest calculation is end time minus start time. With the start time in B2 and end time in C2, =C2-B2 returns a time duration. If the cells contain 9:00 AM and 5:30 PM, the result represents 8 hours and 30 minutes. To show that duration as a decimal number, use =(C2-B2)*24; the result is 8.5.

Enter actual time values, such as 9:00 AM or 5:30 PM, rather than text that merely looks like a time. Excel typically recognizes entries such as 9:00 or 17:30 as times, depending on regional settings. If a value is left-aligned or calculations return an error, it may have been stored as text. Re-enter it in a recognized time format or use Excel’s data-conversion tools before relying on the result.

Calculate shifts that cross midnight

A basic subtraction gives a negative result when a shift begins in the evening and ends after midnight. For example, 10:00 PM to 6:00 AM crosses into the next day. If you have time-only entries and know a shift is shorter than 24 hours, MOD handles the rollover: with start time in B2 and end time in C2, =MOD(C2-B2,1)*24 returns 8 for that example.

MOD wraps the difference into a range from zero up to, but not including, one full day. That makes it useful for ordinary overnight shifts, but it assumes the intended elapsed time is under 24 hours. If a shift could last 24 hours or more, time-only values cannot identify its true duration. Include both the date and time for each timestamp instead.

When B2 contains a complete start date and time and C2 contains a complete end date and time, use =(C2-B2)*24. For a start of November 2 at 10:00 PM and an end of November 3 at 6:00 AM, the result is 8. The dates make the elapsed time explicit, so MOD is unnecessary and could conceal an incorrect date entry.

Choose between decimal hours and hours:minutes

Decimal hours and elapsed time are two different display choices for the same duration. Decimal hours are useful when a report needs values such as 8.5 hours. Hours:minutes are often easier to read on a timesheet because they show 8:30. Changing a cell’s display format does not change the underlying value, so choose a format that matches how you plan to use the result.

For a decimal-hours result, select the formula cell and use Home > Number to choose Number, then set the desired decimal places. For an elapsed-time display, format the cell as a custom duration. In Excel for Microsoft 365 on Windows, select the cell, press Ctrl+1, choose Number > Custom, and enter [h]:mm in the Type box. The square brackets allow the displayed hours to continue past 24; ordinary h:mm formatting can restart the hour display after each day.

For example, if a total represents 27 hours and 15 minutes, the custom format [h]:mm displays 27:15. A decimal-hours display would show 27.25. On Excel for Mac, open the Format Cells dialog through Format > Cells and set the custom format there; keyboard shortcuts can differ by keyboard and Excel version. Excel for the web offers number-format controls, though custom-format availability and dialog details can vary. If the workbook will be used in different Excel versions, check that the chosen display is preserved when opened there.

Subtract unpaid breaks

To subtract a break, make the break duration a separate value rather than adjusting the start or end time by hand. Suppose B2 contains 9:00 AM, C2 contains 5:30 PM, and D2 contains a 30-minute break entered as 0:30. With all three entries stored as times, =(MOD(C2-B2,1)-D2)*24 returns 8 net hours. The MOD part accommodates an overnight shift; the break is subtracted while it is still expressed as a fraction of a day.

For a shift recorded with full date-and-time values, use the same units consistently. With the start timestamp in B2, end timestamp in C2, and break duration in D2 as an Excel time value, =((C2-B2)-D2)*24 returns net decimal hours. If the break is instead entered as a decimal number of hours, subtract it after converting the elapsed time: with 0.5 in D2, =(C2-B2)*24-D2 returns the elapsed hours less half an hour.

Choose one break convention and label the column clearly. A value of 0:30 is a time duration; a value of 0.5 is decimal hours. Confusing the two can create a substantial error because Excel interprets 0.5 as half a day when it is treated as a time value. Also decide how to handle multiple breaks: you can enter their combined duration in one cell or use separate columns and subtract each duration.

Build a daily and weekly timesheet

A practical sheet keeps inputs and calculated results in separate columns. For instance, use A for the work date, B for start time, C for end time, D for break duration, and E for net hours. Put a clear header in row 1, then enter one shift per row. In E2, use =(MOD(C2-B2,1)-D2)*24 when shifts are under 24 hours and D contains a time-formatted break; fill the formula down for the other rows. The formula returns decimal hours, so format E as Number rather than as a clock time.

To total five daily decimal-hour results in E2:E6, enter =SUM(E2:E6) in the weekly total cell. If those values add to 40.5, the result is 40.5 hours. If instead each daily result is stored as an elapsed-time value without multiplying by 24, summing time values requires a duration format such as [h]:mm so totals above 24 hours remain visible.

For recurring sheets, convert the range to an Excel table using Insert > Table. Tables make it easier to keep headers visible, filter rows, and extend formulas when new rows are added. Verify that the calculated column formula fills into new rows as expected, especially if the workbook has been configured not to use calculated columns. Protecting input cells or using data validation can also reduce accidental changes, but neither replaces checking the entered dates and times.

Overtime, rounding, and payroll context

Excel can calculate a threshold-based overtime estimate, but a formula cannot determine which employment rules or workplace agreement applies. Rules may depend on jurisdiction, workweek definition, daily limits, breaks, pay categories, and other factors. Confirm the applicable policy before using a spreadsheet total for pay. A simple weekly threshold is only an arithmetic example, not a complete payroll rule.

With the weekly total in F2 and a hypothetical 40-hour threshold, the following formula returns hours above that threshold: =MAX(0,F2-40). If F2 is 43.25, the result is 3.25; if F2 is 38, it returns zero. This does not calculate an overtime pay rate or decide whether particular hours qualify. Keep the threshold in a labeled input cell instead of embedding it in a formula if it may change.

Rounding is another policy decision. To round one decimal-hours result in E2 to two decimal places, use =ROUND(E2,2). For example, 7.6667 becomes 7.67. Rounding each shift before adding can produce a slightly different total from adding the unrounded shifts and rounding once at the end. Decide which method is required, preserve the underlying precision where possible, and document the approach so totals can be reviewed.

Common formula problems and how to fix them

The result shows a clock time instead of hours. A duration such as 8.5 may appear as a time if the cell is formatted as Time. Apply Number format for decimal hours, or use [h]:mm for elapsed hours and minutes.
An overnight shift returns a negative value or ####. For time-only entries under 24 hours, use the MOD formula. If dates are available, include them in both timestamps and subtract the complete date-and-time values.
The formula returns an unexpected number. Check whether the break is entered as 0:30 or 0.5 and use a formula that matches its units. Also check that end time is later than start time for same-day calculations and that the dates are correct.
The formula appears as text instead of calculating. Confirm that the cell does not have Text format, that the formula begins with an equals sign, and that there is no apostrophe before it. Re-enter the formula after setting a suitable number format.
A total is wrong even though daily calculations look right. Check for blank or text entries, duplicated shifts, missing breaks, and inconsistent date ranges. A weekly total should include only the intended rows and workweek.

For Windows, Ctrl+1 opens Format Cells, and Ctrl+Z undoes ordinary worksheet edits. On Mac, use the Format Cells command available in the Format menu if the Windows shortcut is not applicable. Be cautious when copying formulas: relative references such as B2 and C2 adjust as a formula is filled down, while absolute references with dollar signs do not. Excel’s formula bar and a quick check of one known shift can help identify reference mistakes.

Frequently Asked Questions

What is the simplest Excel formula for hours worked?
For a same-day shift with start in B2 and end in C2, use =(C2-B2)*24 for decimal hours. Use a duration number format if you would rather display hours and minutes.

How do I calculate hours worked overnight in Excel?
For time-only entries in B2 and C2 and a shift shorter than 24 hours, use =MOD(C2-B2,1)*24. If you have the dates as well, enter full date-and-time values and subtract those timestamps so the elapsed duration is unambiguous.

How do I subtract a 30-minute lunch break?
Enter the break as 0:30 in D2, with start time in B2 and end time in C2. Use =(MOD(C2-B2,1)-D2)*24 for net decimal hours on a shift shorter than 24 hours. Make sure D2 is a time duration, not the decimal number 0.5.

Why does Excel show 8:30 instead of 8.5?
The result cell is likely formatted as a time or duration. Choose Number format to show decimal hours. Conversely, format a decimal-hours result only after converting it appropriately; simply changing 8.5 to a time format does not mean 8 hours and 30 minutes.

Can I add hours worked across a week?
Yes. Use SUM on the cells containing daily results. Decimal-hour values can be summed directly as numbers; elapsed-time values should use a duration format such as [h]:mm so the displayed total does not roll over after 24 hours.

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.