Calculating Hours Worked in Excel: Formulas & Tips

Formulas & functions · Updated November 19, 2026

To calculate hours worked in Excel, put a shift’s start time in A2 and end time in B2, then enter =MOD(B2-A2,1)*24 in C2. The formula returns decimal hours, correctly treating an end time after midnight as the next day; for a 9:00 a.m. to 5:30 p.m. shift, it returns 8.5 hours.

The basic hours-worked formula

Excel stores times as fractions of a 24-hour day. Subtracting one time from another therefore produces a fraction of a day: eight hours is one-third of a day. Multiplying the difference by 24 converts it to hours. The MOD function in the basic formula keeps the result positive when a shift crosses midnight, as long as the shift is less than 24 hours and the cells contain times without dates.

To set up a simple worksheet, use one row per shift:

  1. Enter the shift start time in A2 and the end time in B2. You can enter values such as 9:00 AM and 5:30 PM.
  2. Enter the formula in C2. For start time in A2 and end time in B2, use =MOD(B2-A2,1)*24.
  3. Format C2 as a number, rather than as a time, so the result displays as 8.5 instead of a clock reading.
  4. Copy the formula down for other shifts. Excel adjusts the row references automatically.

In Excel for Microsoft 365 on Windows, select the result cells and choose Home > Number Format dropdown > Number. Increase or decrease the decimal places as needed. The formula calculates elapsed time; it does not determine whether a break is paid or whether any particular rounding or overtime policy applies.

Choose the right formula for your time data

The right calculation depends on whether the shift times include calendar dates. For same-day shifts, simple subtraction is enough. With start time in A2 and end time in B2, enter =(B2-A2)*24. For 9:00 a.m. to 5:30 p.m., the result is 8.5. This version can return a negative value if the end time is earlier than the start time, so it is not the best choice for times-only overnight shifts.

For a times-only shift that may pass midnight, use the MOD version from the opening example. For example, if A2 contains 10:15 PM and B2 contains 6:45 AM, with times in A2 and B2 and no dates, =MOD(B2-A2,1)*24 returns 8.5. MOD treats the end time as occurring on the following day. However, because it wraps at 24 hours, it is not suitable for calculating a shift lasting 24 hours or longer.

If both cells include full date-and-time values, subtract the complete timestamps instead. With a start date and time in A2 and an end date and time in B2, use =(B2-A2)*24. For example, a shift starting at 10:15 PM on Monday and ending at 6:45 AM on Tuesday returns 8.5. Including dates makes the order of the events explicit and avoids the 24-hour wraparound limitation.

Subtract unpaid breaks

To deduct an unpaid break, keep the break duration in a separate cell. This makes the worksheet easier to review and lets you change the break without editing the calculation. Enter the break as a duration, such as 30 minutes in time format, rather than as the number 30. Excel interprets a time value as part of a day.

Assume A2 contains the start time, B2 the end time, and C2 the break duration, all entered as time values. To calculate paid hours as a decimal, enter =MOD(B2-A2,1)*24-C2*24. A 9:00 a.m.–5:30 p.m. shift with a 30-minute break returns 8. The first part calculates 8.5 elapsed hours, and the second converts the half-hour break to 0.5 hours before subtracting it.

For a shift with actual dates as well as times, subtract the break duration from the timestamp difference. With start date and time in A2, end date and time in B2, and break duration in C2, use =(B2-A2-C2)*24. This assumes C2 is a duration value, such as 0:30, not a text entry. Check that the break is shorter than the shift; otherwise, the calculated paid time may be zero or negative, indicating an input or data issue.

Display hours as decimals or as elapsed time

Decimal hours are useful when you need to add hours or multiply them by an hourly rate. For example, 7 hours and 30 minutes is 7.5 hours, not 7.30 hours. The digits after the decimal point represent a fraction of an hour: 0.5 is 30 minutes and 0.25 is 15 minutes. Format decimal-hour results as Number and choose a suitable number of decimal places.

You can instead keep the result as an Excel time value and display it as hours and minutes. With start time in A2 and end time in B2, enter =MOD(B2-A2,1), then apply a duration format. In Windows desktop Excel, select the cells, press Ctrl+1, choose Number > Custom, and enter [h]:mm as the custom format. The square brackets allow hours to accumulate past 24; without them, a total can wrap around and appear as a time on a clock.

On Excel for Mac, Command+1 opens the Format Cells dialog; the number-format choices are similar, though the dialog layout can differ. Excel for the web supports common time calculations and formats, but its formatting controls may be arranged differently from the Windows desktop ribbon. Whichever version you use, keep the underlying result as a numeric time value if you intend to total it later.

Worked example: calculate a week of shifts

Suppose each row represents one shift. Put the start time in column A, end time in B, unpaid break duration in C, and paid hours in D. Enter time values consistently, such as 8:30 AM, 5:00 PM, and 0:30 for a half-hour break. This example assumes each shift is under 24 hours and the times do not include dates.

With start time in A2, end time in B2, and break duration in C2, calculate the paid hours in D2 using =MOD(B2-A2,1)*24-C2*24. For 8:30 a.m. to 5:00 p.m. with a 30-minute break, the elapsed shift is 8.5 hours and the result is 8.0 paid hours. Copy the formula down for the remaining shift rows.

With daily paid-hour results in D2:D6, total the five shifts by entering =SUM(D2:D6) in D7. If the five daily results are 8, 8, 8, 8, and 8.5, the weekly total is 40.5 hours. Format the total as Number. If instead the daily calculations are stored as time values, sum those durations and use the custom format [h]:mm to show totals such as 40:30 rather than a wrapped clock time.

Overnight shifts and date-based records

Times-only entries can be convenient for a basic rota, but they do not record which day a shift began or ended. A row with 10:00 PM and 6:00 AM relies on the MOD formula to infer an overnight shift. That works for ordinary overnight shifts shorter than 24 hours, but it cannot distinguish a shift ending the same morning from one ending a day later.

For records where the date matters, include date and time in both the start and end cells. Format the cells to show both, for example a date plus a time, and use the timestamp subtraction formula. A shift beginning on one date and ending the next then calculates correctly without relying on a wraparound assumption. This is also clearer when reviewing shifts that span weekends, holidays, or multiple calendar days.

Be consistent about the date and time system in the data. A date entered as text may look correct but fail to subtract as expected. If Excel does not recognize an entry as a date or time, re-enter it in a format Excel accepts for the workbook’s regional settings, or convert the source data before calculating. Do not use the times-only MOD formula when a recorded duration could be 24 hours or more.

Common formula errors and how to fix them

  • A decimal looks like a clock time: If a result displays as 8:30 rather than 8.5, the cell may have a time format. Change it to Number for decimal hours.
  • A total resets after 24 hours: The calculation may be correct but formatted as a clock time. Apply the custom elapsed-time format [h]:mm to a time-value total.
  • An overnight result is negative: If the cells contain times only, use the MOD formula. If they contain full timestamps, check that the end date is the correct date and use timestamp subtraction.
  • The formula returns an error or unexpected result: Check that start, end, and break entries are genuine Excel time values, not text. Also verify that the break cell is a duration and that the formula refers to the intended row.
  • Minutes are entered as decimals incorrectly: A 30-minute break is 0.5 decimal hours, but in an Excel time cell it is entered as 0:30. Do not enter 0.30 expecting Excel to read it as 30 minutes.

When a formula is copied down, inspect a few rows to confirm that references moved as intended. If some rows contain a different layout or missing values, a copied formula may not represent those cases correctly. For a blank row, it can be better to leave the result blank rather than calculate from empty cells; that requires an additional conditional formula, so make sure the worksheet genuinely needs it before adding complexity.

Rounding, overtime, and interpreting results

Do not round each shift automatically unless the applicable workplace policy or recordkeeping requirement calls for it. Rounding every shift before summing can produce a different weekly total than summing exact durations and rounding once. For example, several small differences can accumulate. Preserve the underlying calculation at full precision and use cell formatting to control how many decimals are displayed; formatting changes appearance, not the stored result.

If you need to apply a threshold to a weekly total, keep the threshold separate from the hours calculation. As an illustrative spreadsheet calculation only, if weekly decimal hours are in E2 and a chosen threshold is 40 hours, the amount above that threshold can be calculated with =MAX(0,E2-40). If E2 is 40.5, this returns 0.5. This is only arithmetic; it does not establish which hours qualify for overtime or what rules apply. Check the relevant workplace policy and requirements with an appropriate professional or authority.

Likewise, an hours-worked formula cannot decide whether a break is paid, how to treat leave, or how to handle a missed punch. Those are separate data and policy decisions. Keep raw start, end, and break entries visible, and avoid overwriting them with calculated values. That makes it easier to trace a total back to the entries used to produce it.

Frequently Asked Questions

What is the simplest Excel formula to calculate hours between two times?
With a start time in A2 and an end time in B2 for a same-day shift, use =(B2-A2)*24 for decimal hours. For times-only shifts that may cross midnight, use =MOD(B2-A2,1)*24.

How do I calculate hours worked overnight in Excel?
With a start time in A2 and end time in B2, and no dates in either cell, use =MOD(B2-A2,1)*24. For shifts that could last 24 hours or more, enter full start and end timestamps and subtract those instead.

How do I subtract a 30-minute lunch break?
With start time in A2, end time in B2, and the break entered as 0:30 in C2, use =MOD(B2-A2,1)*24-C2*24 to return decimal hours. Confirm whether the break should be deducted for your particular records.

How can I show a total of 40 hours and 30 minutes?
If the hours are stored as time values, format the total cell with the custom number format [h]:mm. The square brackets prevent Excel from restarting the display at 0 after 24 hours.

Why does Excel show 8.5 hours instead of 8 hours and 30 minutes?
That is decimal-hour notation: 0.5 of an hour equals 30 minutes. If you prefer an hours-and-minutes display, calculate a time value without multiplying by 24 and format it as [h]:mm.

Related

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.