Excel Formula for Adding Hours on a Timesheet Correctly

Formulas & functions · Updated November 19, 2026

To add timesheet hours in Excel, use =SUM(B2:B8) when the daily durations are in cells B2 through B8. Then format the total cell with the custom number format [h]:mm so the display keeps counting past 24 hours instead of starting over at zero.

The basic formula for a timesheet total

Enter each day’s worked duration in one cell, such as B2:B8, and put the sum in the cell below or beside the entries. With durations in B2:B8, the formula is =SUM(B2:B8). Excel stores time as a fraction of a day, so SUM adds the underlying values; the formula does not need to convert each entry first.

For example, suppose B2 contains 7:30, B3 contains 8:00, B4 contains 6:45, and B5 contains 8:15, while B6:B8 are blank. The total is 30 hours and 30 minutes. If the result cell uses a regular time-of-day format, Excel may show 6:30 because 30 hours and 30 minutes is one full day plus 6 hours and 30 minutes. The total itself is still correct; the display format is the problem.

SUM is usually preferable to typing a chain such as =B2+B3+B4+B5. It is easier to extend when you add days, and blank cells in the specified range do not change the result. Check that the formula’s range covers all the intended rows and does not include a previous total, which would count those hours twice.

Format totals to show more than 24 hours

Excel’s ordinary time formats show a clock time within a 24-hour day. A timesheet total, however, is an elapsed duration, so it needs an elapsed-time format. Select the total cell, press Ctrl+1 on Windows (Command+1 on Mac), choose the Number tab and then Custom, enter [h]:mm in the Type box, and select OK. The square brackets tell Excel to display cumulative hours rather than reset the hour count after each day.

With the example above, the SUM result displays as 30:30. The same format works for weekly totals such as 42:15. If you need seconds, use [h]:mm:ss. By contrast, formats such as h:mm or hh:mm are generally clock-time formats and can wrap after 24 hours. If a total appears unexpectedly small, confirm the format before assuming the formula is wrong.

The bracketed format changes how a value appears, not the stored value or the arithmetic. Excel continues to treat the result as a number of days. That matters if the result will be multiplied by an hourly rate or compared with another duration: use the underlying value or convert it to hours as described below, rather than relying on the characters shown in the cell.

Calculate each shift from start and end times

A timesheet may record clock-in and clock-out times rather than daily durations. For a shift that starts and ends on the same date, assume the start time is in B2 and the end time is in C2; the duration formula in D2 is =C2-B2. Format D2 as h:mm or [h]:mm. For example, a shift from 9:00 to 17:30 returns 8:30 before subtracting any unpaid break.

To calculate multiple rows, enter the formula for the first row and fill it down. On Windows, select the formula cell and drag its fill handle down the column; double-clicking the fill handle can fill adjacent rows when Excel detects the data range. On Mac, the fill handle works similarly. Check a few copied formulas to make sure their row references changed as intended.

If a shift can cross midnight, a simple subtraction can produce a negative result. For a shift with start in B2 and end in C2, where the end time is earlier than the start because the shift passes midnight, use =MOD(C2-B2,1). A shift from 22:00 to 6:00 returns 8:00. This formula assumes the shift lasts less than 24 hours and that the cells contain times without dates. For longer or multi-day shifts, record the actual start and end dates as well as times and subtract the full date-time values instead.

Subtract unpaid breaks

If the start time is in B2, the end time is in C2, and an unpaid break duration is in D2, use =C2-B2-D2 in E2 for a same-day shift. For an 8:30 a.m. to 5:00 p.m. shift with a 0:30 break, the result is 8:00. Format the duration cell as h:mm, and use [h]:mm for any total across shifts.

For an overnight shift with the same columns, use =MOD(C2-B2,1)-D2. A 22:00-to-6:00 shift with a 0:30 break gives 7:30. Enter the break as a duration, such as 0:30, rather than as the time of day when the break began. If you need to subtract several breaks, add their durations in a separate total-break cell or subtract each duration in the formula.

Decide what the break column means before building a template. A blank break cell is treated as zero in subtraction, but a text entry such as “none” is not a numeric duration. Consistent entries make formulas easier to audit and help prevent missing or incorrectly entered breaks from silently affecting totals.

Add daily totals into a weekly total

Once each shift’s net duration is calculated in column E, assume the seven daily totals are in E2:E8. The weekly formula is =SUM(E2:E8). Format the result as [h]:mm. For instance, daily durations of 8:00, 7:30, 8:15, 8:00, and 7:45 sum to 39:30; the two blank weekend rows do not change that result.

Keeping one row per shift or day makes it easier to review the source of a weekly number. For employees with multiple shifts in a day, calculate each shift on its own row and sum all the relevant duration cells. You can also use a wider range that includes future rows, but avoid including the weekly total cell in its own SUM range, as that creates a circular reference.

For a timesheet covering several weeks, create a separate weekly total or use clear date ranges so each period can be checked independently. A single large SUM can add all entries, but it will not identify which hours belong to a particular week or pay period.

Convert a duration to decimal hours

Some payroll or reporting systems request hours as a decimal, such as 8.5 rather than 8:30. Excel’s stored duration is a fraction of a day, so multiply by 24 to express it in hours. With a duration in E2, use =E2*24 in another cell. If E2 is 8:30, the result is 8.5. Format the result as General or Number rather than as a time.

For a total, assume the weekly duration is in E9; use =E9*24 to convert that total to decimal hours. A total of 39:30 becomes 39.5 hours. Keep the original duration column if you need to show minutes clearly, and use a separate decimal column for systems that request decimal hours.

Do not confuse 8:30 with 8.30 decimal hours. Thirty minutes is half an hour, so 8:30 equals 8.5 hours, not 8.3. Whether a decimal value should be rounded for payroll depends on the employer’s rules and applicable requirements; the conversion formula itself does not decide how time should be rounded or compensated.

Common timesheet formula problems

  • The weekly total resets after 24 hours: Keep the SUM formula and change the total’s format to [h]:mm. A clock-style format can hide the cumulative hours.
  • The total is zero or unexpectedly low: Check whether entries are actual Excel times or durations rather than text. Re-enter a sample value and confirm the cell is recognized as a number; imported text may need to be converted before arithmetic works.
  • An overnight shift shows a negative duration: Use the MOD formula for a time-only shift under 24 hours, or include dates in both start and end cells and subtract the full date-time values.
  • A result includes an extra day: Check for an end time entered with an unintended date, a duplicated shift, or a formula range that includes a total.
  • Minutes appear slightly inconsistent: Excel stores times as fractions of a day, and displayed formats can hide seconds or fractions. Show seconds temporarily or inspect the source entries before changing precision or rounding.

One useful check is to compare a manually calculated sample shift with the formula result, including its break. Also inspect the formula bar when selecting a cell: it helps distinguish a real time value from text that merely looks like a time. A carefully formatted total is not proof that every input row is correct.

Build a simple, reviewable timesheet

A practical layout might use columns for date, start, end, unpaid break, and net duration, with a weekly total below the duration column. Label the columns and use one consistent time-entry convention, such as 24-hour clock entries. Protecting formulas from accidental edits can help in a shared workbook, but keep an editable copy and make sure users know which cells are intended for data entry.

Excel’s table feature can make a growing list of shifts easier to manage, but a basic range and SUM formula are sufficient for a small weekly sheet. If you copy a template for a new week, verify the dates, formula ranges, break entries, and total-cell format. On Excel for the web, the same basic SUM and time-value calculations apply; the steps and available controls for custom formatting can look different from the Windows desktop dialog.

Timesheet arithmetic only calculates what the entries describe. It does not determine whether a break is legally required, whether time should be rounded, or how overtime is defined. Use the relevant workplace policy and rules for those decisions, and keep the underlying start, end, and break records when the totals need to be reviewed.

Frequently Asked Questions

What is the Excel formula to add hours and minutes?
For durations in B2:B8, use =SUM(B2:B8). Format the result as [h]:mm if the sum may exceed 24 hours.

Why does my Excel timesheet total show the wrong number after 24 hours?
The total may be displayed with a clock-time format that wraps at one day. Change the result cell to the custom format [h]:mm; this changes the display, not the calculation.

How do I calculate hours for an overnight shift?
For a time-only start in B2 and end in C2, use =MOD(C2-B2,1) when the shift lasts less than 24 hours. If the shift spans multiple dates, enter full dates and times and subtract the end date-time from the start date-time.

How do I subtract a lunch break from hours worked?
For a same-day shift with start in B2, end in C2, and break duration in D2, use =C2-B2-D2. For an overnight shift under 24 hours, use =MOD(C2-B2,1)-D2.

How do I turn 7 hours and 45 minutes into decimal hours?
If the duration is in E2, use =E2*24. The result is 7.75. Format the result as a number, because a time format would display it as a clock-style value rather than decimal hours.

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.