Working Hours Calculation Formula in Excel: Examples

Formulas & functions · Updated November 20, 2026

For a shift that starts in A2 and ends in B2 on the same day, calculate working hours with =(B2-A2)*24. If C2 contains an unpaid break, use =(B2-A2-C2)*24; the result is a decimal number of hours, such as 8.5.

The basic working-hours formula

Excel stores times as fractions of a 24-hour day. For example, 12 hours is one-half of a day, and 6 hours is one-quarter. Subtracting a start time from an end time therefore produces a fraction, not a number of hours. Multiplying the difference by 24 converts it to hours.

With a start time of 9:00 AM in A2 and an end time of 5:30 PM in B2, enter this formula in D2:

For start time in A2 and end time in B2 on the same date, use =(B2-A2)*24. The result is 8.5 hours, equivalent to 8 hours and 30 minutes.

Enter actual time values in the start and end cells, rather than typing words such as morning or evening. Excel accepts entries such as 9:00 AM and 5:30 PM; it can also display them in a 24-hour style such as 09:00 and 17:30. Once the formula is in the first result row, fill it down for the other shifts using the fill handle or copy and paste.

Set up a clear timesheet layout

A simple layout makes formulas easier to check and extend. One practical arrangement is:

  • Column A: shift start time
  • Column B: shift end time
  • Column C: unpaid break duration
  • Column D: net hours worked

Put descriptive headings in row 1 and the first shift in row 2. In this layout, a break should be entered as a duration, such as 0:30 for 30 minutes, not as a clock time such as 12:30 PM. For a shift from 9:00 AM to 5:30 PM with a half-hour unpaid break, C2 is 0:30.

With start in A2, end in B2, and break duration in C2, use =((B2-A2)-C2)*24. The result is 8 hours for the example shift: 8.5 hours elapsed minus 0.5 hour of break. A paid break should not be subtracted if the purpose is to calculate paid working time.

Use consistent data entry throughout the sheet. A cell that looks like a time may actually be text, especially if it was imported from another system. If Excel will not calculate a row as expected, select the cells and check that Excel recognizes the entries as times; re-entering a value or converting imported text may be necessary.

Handle shifts that pass midnight

A direct subtraction becomes negative when a shift begins late in the evening and ends after midnight. For example, subtracting 10:00 PM from 6:30 AM does not, by itself, describe an 8.5-hour overnight shift. When the cells contain clock times only and the shift lasts less than 24 hours, MOD can account for the day boundary.

With start time in A2 and end time in B2, and both entered as clock times without dates, use =MOD(B2-A2,1)*24. For a 10:00 PM start and 6:30 AM finish, the result is 8.5 hours.

With the same start and end cells plus an unpaid break duration in C2, use =(MOD(B2-A2,1)-C2)*24. If C2 is 0:30, the overnight example returns 8 hours. MOD wraps the negative time difference into the remainder of a 24-hour day, and the break is then deducted.

There is an important limitation: MOD treats the interval as a daily clock-time calculation. It is suitable for a single shift shorter than 24 hours, but it discards whole days. If your entries include dates as well as times, use the full date-and-time values and subtract them directly instead. This is more reliable for shifts longer than a day or records that cross multiple dates.

Subtract breaks and distinguish paid time

Whether a break belongs in the calculation depends on what the result is meant to represent. A net-hours column commonly excludes unpaid meal breaks, while a paid-hours report may include paid breaks. Make that choice explicit in the column heading and in the instructions for anyone entering data. A formula cannot tell whether a particular break is paid.

For a single break, enter its elapsed length in C2. For example, enter 0:45 for a 45-minute break. The break cell should represent a duration, so it does not need to correspond to a time of day. You can subtract more than one break by adding separate duration columns or by summing them in the formula.

With start in A2, end in B2, and two unpaid break durations in C2 and D2, use =((B2-A2)-C2-D2)*24. If the shift is 9:00 AM to 5:30 PM and the breaks are 0:30 and 0:15, the formula returns 7.75 hours: 8.5 elapsed hours minus 0.75 hour of breaks.

Check that a break is not longer than the elapsed shift. Otherwise, a formula can produce a negative result. For a managed timesheet, data validation or a visual review can help catch impossible entries; avoid silently changing a negative result to zero unless that is the intended business rule, because doing so can hide an input error.

Display results as hours and minutes or decimal hours

Choose the result format based on how people will use the number. Decimal hours are convenient for adding hours or entering values into systems that expect decimals. An 8.5-hour result means 8 hours and 30 minutes, not 8 hours and 50 minutes. For payroll or human-readable timesheets, a duration displayed as 8:30 may be easier to interpret.

If the formula multiplies by 24, format the result as a number. Select the result cells, then use Home > Number Format and choose Number; set the desired decimal places if needed. The underlying result remains numeric, so it can be summed or used in further calculations.

If you prefer a duration display, leave out the multiplication by 24 and apply a duration format. With a same-day shift starting in A2 and ending in B2 and no break, use =B2-A2. Format the result cells with the custom number format [h]:mm. For the 9:00 AM to 5:30 PM example, the displayed result is 8:30.

The square brackets in [h]:mm matter for totals. A format of h:mm starts counting the hour display over after 24 hours, which can make a weekly total look misleading. The bracketed form shows accumulated hours, so a total of 40 hours appears as 40:00. In Microsoft 365 for Windows, open Format Cells from the Home tab’s Number group, choose Custom, and enter [h]:mm. Excel for Mac also supports custom number formats through its Format Cells controls, although the dialog layout can differ.

Total hours across a week

For a weekly total, use a consistent result type in every daily row. If D2:D6 contains decimal hours, sum those values. If the cells contain Excel duration values displayed with [h]:mm, sum those durations and retain the bracketed format in the total cell.

With decimal daily hours in D2:D6, use =SUM(D2:D6). If the five entries are 8.5, 8, 8.5, 8, and 9, the result is 42 hours. Format the total as a number, not as a clock time.

With duration values in D2:D6, use =SUM(D2:D6) as well, then format the total cell as [h]:mm. Those same daily entries would display as 42:00. The SUM formula is the same; the difference is whether the daily results are decimal hours or day-fraction durations. Do not mix the two representations in one total, because a decimal 8.5 and a duration of 8 hours 30 minutes are stored differently.

For a longer timesheet, keep one row per shift or workday and use a clearly defined weekly range. If people work overnight shifts, make sure the daily formula handles midnight correctly before relying on the weekly total. A correct SUM cannot repair an incorrect shift duration.

Calculate hours over a chosen threshold

Some schedules need a separate column for hours above a daily threshold. This is a spreadsheet calculation, not a determination of which overtime rules apply. Thresholds and rules can vary by workplace and jurisdiction, so use the value that is appropriate for the report and confirm any employment or payroll requirements with a qualified source.

With daily decimal hours in D2 and an illustrative 8-hour threshold, use =MAX(0,D2-8). If D2 is 9.5, the formula returns 1.5; if D2 is 7.5, it returns 0. MAX prevents negative values in the overtime column when the daily hours are below the threshold.

For a different threshold, replace 8 with the intended number of hours. If your policy uses a weekly rather than daily threshold, calculate the weekly total first and apply the threshold to that total instead. Keep regular hours, overtime hours, and total hours in separate columns when the distinction matters, and do not assume that a simple threshold formula captures every real-world rule.

Common calculation errors and checks

When an answer looks wrong, check the inputs and the output format before rewriting the formula. Common causes include:

  • Using a clock format for decimal hours: A result of 8.5 is 8.5 hours. Displaying it as a time of day can make it appear unrelated to the shift.
  • Forgetting the conversion: A subtraction such as B2-A2 returns a fraction of a day. Multiply by 24 for decimal hours, or keep it as a duration and format it as [h]:mm.
  • Not accounting for midnight: A clock-only overnight shift needs the MOD approach, while dated timestamps should be subtracted directly.
  • Entering a break as a time of day: A break value should be a duration such as 0:30. A time such as 12:30 PM represents a point on the clock, not a half-hour interval.
  • Summing durations with h:mm: Use [h]:mm for an accumulated duration over 24 hours so the displayed total does not roll over.
  • Mixing text and numeric times: Imported or pasted values may look like times but be stored as text. Check cell contents and number formats, then convert or re-enter invalid values.

It is also useful to compare a few formula results with hand calculations. Confirm the elapsed time first, subtract each unpaid break, and then compare the result with the spreadsheet. Testing a same-day shift, an overnight shift, and a shift with a break helps reveal whether the layout and formula match the way the data is recorded.

Frequently Asked Questions

How do I calculate hours between two times in Excel?
With start time in A2 and end time in B2 on the same day, use =(B2-A2)*24 for decimal hours. For a duration display instead, use =B2-A2 and format the result as [h]:mm.

How do I calculate working hours minus lunch in Excel?
With start in A2, end in B2, and an unpaid lunch duration in C2, use =(B2-A2-C2)*24. Enter a 30-minute lunch as 0:30. The result is decimal hours.

How do I calculate an overnight shift?
With clock-only start and end times in A2 and B2 for a shift shorter than 24 hours, use =MOD(B2-A2,1)*24. Subtract a break duration in C2 with =(MOD(B2-A2,1)-C2)*24.

Why does my total show a time instead of total hours?
Excel may be displaying a duration using a clock format that wraps after 24 hours. For duration values, apply the custom format [h]:mm. For decimal-hour results, choose Number as the format.

Should I use decimal hours or an hours-and-minutes format?
Use decimal hours when a report or downstream calculation expects numeric hours, such as 8.5 for eight and a half hours. Use [h]:mm when readers should see elapsed time as 8:30. Keep one representation consistent throughout a total.

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.