Excel Working Hours Formula: Calculate Time Worked

Formulas & functions · Updated November 19, 2026

If A2 contains a shift start time and B2 its end time on the same day, enter =(B2-A2)*24 to calculate hours as a decimal; for example, 9:00 AM to 5:00 PM returns 8. Use a different approach for overnight shifts, breaks, or shifts that span calendar dates, and format the result to match how you want to report time.

How the basic working-hours formula works

Excel stores times as fractions of a day: one hour is 1/24, and a full day is 1. That is why subtracting a start time from an end time gives a fraction rather than a number of hours. Multiplying the difference by 24 converts it into decimal hours, which are useful for totaling hours or calculating pay from an hourly rate.

For the basic example, assume A2 contains 9:00 AM and B2 contains 5:00 PM, both on the same date. Enter =(B2-A2)*24 in C2. The result is 8. If the shift is 8:30 AM to 5:00 PM, the result is 8.5, meaning eight and a half hours—not 8 hours and 50 minutes.

Enter start and end values as actual Excel times, not text that only looks like a time. You can type values such as 9:00 AM or 17:00 into cells. If Excel leaves them aligned as text or calculations return an error, check whether the cells contain text rather than recognized time values.

Choose decimal hours or an hours-and-minutes duration

Decimal hours and elapsed-time formatting answer different questions. A decimal result is convenient for calculations: 7.5 means seven and a half hours. A duration such as 7:30 is often easier to read on a timesheet, but it is not the same numeric display. In particular, 7:30 means seven hours and thirty minutes, not 7.30 hours.

For a duration display, assume A2 contains the start time and B2 contains the end time on the same day. Enter =B2-A2 in C2, then format C2 with a duration format such as [h]:mm. In Microsoft 365 for Windows, open Home > Number Format > More Number Formats, choose Custom, and enter [h]:mm in the Type box. On Mac, use Home > Number Format > More Number Formats as well; the Format Cells dialog can also be opened with Command+1 on Mac or Ctrl+1 on Windows.

The square brackets in [h]:mm tell Excel to show accumulated hours beyond 24 instead of restarting the hour count at zero. That matters for a weekly total: a result of 42 hours and 15 minutes should display as 42:15, not as 18:15 on a clock-style display. If you need decimal hours, keep the result numeric and format it as Number with the decimal places you want.

Calculate an overnight shift

A simple subtraction can produce a negative result when a shift ends after midnight. If A2 contains 10:00 PM and B2 contains 6:00 AM, B2-A2 is negative because Excel treats both values as times within the same day. For shifts that are less than 24 hours and have time-only entries, use MOD to wrap the difference past midnight.

Assume A2 contains the start time, B2 the end time, and the shift is no longer than 24 hours. Enter =MOD(B2-A2,1)*24 in C2 to get decimal hours. With 10:00 PM in A2 and 6:00 AM in B2, the result is 8. For an elapsed-time display instead, enter =MOD(B2-A2,1) with the same cell layout and format C2 as [h]:mm; it displays 8:00.

MOD is useful when a time-only end value is earlier than its start value. It is not a substitute for recording dates when a shift could last 24 hours or longer, or when the work period may span more than one midnight. In those cases, enter a full date and time for both endpoints so Excel can calculate the actual elapsed duration.

Use dates for shifts that cross calendar days

A reliable timesheet can store each start and end as a full date-time value. For example, A2 might be November 3 at 10:00 PM and B2 November 4 at 6:00 AM. Excel then has enough information to distinguish an overnight shift from an end time that was entered incorrectly.

Assume A2 contains the complete start date and time, and B2 contains the complete end date and time. Enter =(B2-A2)*24 in C2 to return decimal hours. For the November 3, 10:00 PM to November 4, 6:00 AM example, the answer is 8. For a duration display, use =B2-A2 with that same layout and format the result as [h]:mm.

Make sure the end date is the actual date the shift finished. A date-time value is stored as a number, so formatting the cell to show only a time does not remove its date; it only hides that part of the display. Conversely, if you enter time-only values, Excel has no way to infer the intended date unless your formula makes an assumption, such as MOD treating an earlier end time as the following day.

Subtract an unpaid break

To calculate net working time, subtract the break duration from elapsed shift time. Store the break as a time duration, not as a start or end clock time. For example, enter 0:30 for a 30-minute break. Excel interprets that as half an hour, which can be subtracted from the shift duration.

Assume A2 contains the start time, B2 the end time, and C2 the total unpaid break duration. For a same-day shift, enter =(B2-A2-C2)*24 in D2. If A2 is 9:00 AM, B2 is 5:00 PM, and C2 is 0:30, the result is 7.5 net hours. For a same-day duration display, use =B2-A2-C2 with the same layout and format the answer as [h]:mm; the result is 7:30.

For a time-only overnight shift, assume A2 is the start, B2 is the end, and C2 is the break duration. Enter =(MOD(B2-A2,1)-C2)*24 in D2. A 10:00 PM to 6:00 AM shift with a 30-minute unpaid break returns 7.5. If you use full date-time values instead, subtract the break duration directly from the end-minus-start difference. Decide whether a break is paid or unpaid according to the relevant workplace policy and applicable rules; the spreadsheet formula cannot determine that.

Copy the formula down and total the hours

Once the first row calculates correctly, copy the formula down for the remaining shifts. In Excel for Microsoft 365 on Windows, select the formula cell and drag its fill handle (the small square at the lower-right corner) down the column, or copy and paste it. Excel adjusts relative references for each row, so a formula referring to A2 and B2 becomes one referring to A3 and B3 on the next row.

For a weekly total, assume decimal daily hours are in D2:D6, one entry per workday. Enter =SUM(D2:D6) in D7. If those five rows contain 8, 7.5, 8, 6, and 8 hours, the total is 37.5 hours. If the daily results are durations instead, use the same SUM formula over the duration cells, then format the total as [h]:mm so it displays correctly when the sum exceeds 24 hours.

Keep the input columns consistent. A useful layout is one column for the date, one for the start time, one for the end time, one for break duration, and one for calculated net hours. Label whether the result is gross time or time after breaks, and use a separate total row. Clear labels make it easier to audit a timesheet and reduce confusion when another person opens the workbook.

Common errors and how to fix them

  • A negative result: With time-only entries, this often means the end time is after midnight. Use the overnight MOD calculation for shifts under 24 hours, or enter full date-time values.
  • A result such as 0.3333 instead of 8: Excel is showing a fraction of a day. Multiply the time difference by 24 for decimal hours, or retain the fraction and apply a duration format.
  • A result that looks like a clock time: The formula may be returning a valid duration, but the cell uses a time-of-day display. Apply [h]:mm for elapsed hours, or multiply by 24 and use a Number format for decimal hours.
  • #### in a cell: The column may be too narrow to show the displayed value. Widen it first; also check that the formula has not produced an unintended negative duration.
  • Break subtraction gives an unexpected answer: Confirm that the break cell contains a duration such as 0:30, rather than a clock time such as 12:30 PM. Check that the break is entered in the same unit convention as the other time values.
  • Hours appear rounded: Increase the number of decimal places in the cell format, or use a duration display. Formatting changes what you see, not the underlying calculation.

Also check for inconsistent dates and blank cells. A blank end time can behave like zero in arithmetic, so a row may show an implausible value before the shift is complete. You can leave the result cell blank until both inputs are present, or visually flag incomplete rows with conditional formatting. For a simple manual check, compare the displayed start and end values, confirm the intended date for overnight work, and recalculate one row by hand.

When to use a time clock instead of a formula

Excel is suitable for straightforward records where someone enters consistent start times, end times, and breaks. A formula can calculate elapsed time, but it does not verify that a person actually worked the recorded shift, apply a company’s rounding policy, or decide which breaks count as paid time. Those are separate decisions that should be documented rather than hidden in an unexplained calculation.

For larger teams, changing schedules, approvals, or audit requirements, a dedicated time-and-attendance system may reduce manual entry and provide controls that a basic worksheet lacks. Excel can still be useful for reviewing exported data, summarizing totals, and checking calculations. If the workbook feeds payroll, verify the resulting records against the organization’s procedures and the actual rules that apply; do not assume that a mathematically correct duration alone establishes a correct payroll result.

Frequently Asked Questions

What is the formula for hours worked in Excel?
For a same-day shift with start time in A2 and end time in B2, subtract the start from the end and multiply by 24 to get decimal hours. Use a duration format instead if you want a display such as 8:00.

How do I calculate hours worked overnight in Excel?
With time-only start and end values and a shift under 24 hours, use MOD to wrap the elapsed time across midnight. If the shift can span multiple dates or last 24 hours or more, enter full date-time values instead.

How do I subtract a lunch break?
Enter the break length as a duration, such as 0:30, in its own cell, then subtract it from the elapsed shift time. Multiply the remaining fraction of a day by 24 if your final answer should be decimal hours.

Why does Excel show 7.5 hours instead of 7:30?
Those are two formats for the same duration: 7.5 decimal hours equals seven hours and thirty minutes. Use a Number format for 7.5 or a duration format such as [h]:mm for 7:30.

How do I add more than 24 hours in Excel?
Sum the duration values, then format the total as [h]:mm. Without square brackets, an ordinary clock-style format can roll over after 24 hours and make a weekly total look smaller than it is.

Can Excel calculate hours from start and end dates as well as times?
Yes. Store the full date and time in both cells, subtract the start date-time from the end date-time, and multiply by 24 for decimal hours. This is the clearest method when a shift crosses midnight or covers multiple days.

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.