Excel Sum Hours: Formulas, Time Formats, and Fixes

To sum elapsed hours in Excel, add the duration cells with =SUM(B2:B5), then format the total as [h]:mm so hours keep accumulating beyond 24. For example, four entries totaling 31 hours and 30 minutes will display as 31:30 rather than wrapping to 7:30.

How Excel stores hours

Excel treats times and durations as parts of a day. One full day is stored as 1, one hour as 1/24, and one minute as 1/1,440. A cell displaying 8:30 therefore represents eight and a half hours, or 8.5/24 of a day. This underlying number is why Excel can add time values, subtract start and finish times, and format the results in different ways.

The display format does not usually change the stored value. A value can appear as 8:30, 8.50, or 0.35417 depending on its number format, even though it represents the same duration. Keep that distinction in mind when a result looks wrong: the calculation may be correct while the display format is not.

Sum durations with the SUM function

Put each duration in its own cell, such as 7:30, 8:15, 6:45, and 9:00 in B2:B5. With those entries in B2:B5, enter =SUM(B2:B5) in B6. The arithmetic is 7 hours 30 minutes + 8 hours 15 minutes + 6 hours 45 minutes + 9 hours = 31 hours 30 minutes. Format B6 as [h]:mm to show 31:30.

To enter a duration, type it as hours and minutes, using a colon, such as 2:45 for two hours and 45 minutes. Excel will generally recognize it as a time value when the entry is valid and the cell is not being treated as text. You can select the cells and choose a suitable time format before entering data, which makes the intended display clearer. For a duration with seconds, enter a value such as 2:45:30.

The SUM function adds the numeric values in the selected range and ignores blank cells. It will not add a value that is stored as text, even if the text looks like a time. If one entry is unexpectedly excluded, check whether Excel recognized it as a number: select the cell and inspect its alignment and the formula bar, or test it in a calculation. Imported data and entries with leading apostrophes are common reasons a time remains text.

Format totals that exceed 24 hours

A normal time format such as h:mm displays the time on a 12- or 24-hour clock. It does not show an accumulating duration. Once a sum reaches a full day, the display can wrap: 31 hours 30 minutes may appear as 7:30 because Excel is showing the time 7 hours and 30 minutes into the next day. That can make a perfectly valid total look like a faulty calculation.

Use a custom elapsed-time format instead. Select the total cell, press Ctrl+1 on Windows (Command+1 in Excel for Mac), choose the Number tab and Custom, then enter [h]:mm in the Type box and confirm. Square brackets around h tell Excel to show total elapsed hours rather than resetting the hour count after each day. The format changes how the value is displayed, not the value itself.

Use [h]:mm for elapsed hours and minutes, such as 31:30.
Use [h]:mm:ss when seconds matter, such as 31:30:45.
Use h:mm AM/PM for a clock time of day, not for a total duration.

To apply a format to several cells, select the range first and then use the same Format Cells steps. On Windows, Ctrl+1 opens Format Cells; on Mac, Command+1 is the corresponding shortcut. Excel for the web has fewer formatting controls in some contexts, so if the custom format option is not available in the browser, open the workbook in the desktop app to set it.

Show a total as decimal hours

Times are stored as fractions of a day, so a total of 31:30 is stored as 31.5/24. Multiply the sum by 24 to express it as a decimal number of hours. With durations in B2:B5, enter =SUM(B2:B5)*24 in C2. For the example entries above, the result is 31.5. Format C2 as General or Number, not as a time, or Excel may display the decimal as a clock value.

Decimal hours are useful for payroll systems, cost calculations, or reports that expect a number such as 7.5 rather than 7:30. The two formats mean the same duration but are not interchangeable without conversion. For example, 7 hours 30 minutes equals 7.5 decimal hours, while 7.30 decimal hours equals 7 hours and 18 minutes—not 7 hours and 30 minutes.

If the source data is already entered as decimal hours, do not multiply the sum by 24. With decimal values 7.5, 8.25, 6.75, and 9 in B2:B5, enter =SUM(B2:B5) in B6 and format it as Number. The result is 31.5 decimal hours. Applying a time format to those numbers would instead treat each number as days, so the result would be misleading.

Calculate hours from start and finish times

For a shift that begins and ends on the same date, subtract the start time from the finish time. If A2 contains a start time of 9:00 and B2 contains a finish time of 17:30, enter =B2-A2 in C2. The result is 8:30. Format C2 as [h]:mm if the result will be included in a duration total.

A shift crossing midnight needs a different calculation if only clock times are recorded. With a start time of 22:00 in A2 and a finish time of 6:00 in B2 on the following day, enter =MOD(B2-A2,1) in C2. The result is 8:00. MOD wraps a negative time difference into the next day, which is useful for overnight shifts.

That shortcut assumes the shift lasts less than 24 hours. If a task can span a full day or more, record both the date and time for the start and finish, then subtract the full date-and-time values. For example, if A2 contains the start date and time and B2 contains the actual finish date and time, enter =B2-A2 in C2 and format C2 as [h]:mm. The full date-time difference preserves multiple days; clock times alone cannot tell Excel whether a finish happened later that day or several days later.

Handle hours and minutes entered separately

Some timesheets store whole hours and extra minutes in separate columns. If A2 contains 7 hours and B2 contains 30 minutes, enter =A2/24+B2/1440 in C2. The result represents 7:30; format C2 as [h]:mm. Dividing hours by 24 and minutes by 1,440 converts both components into Excel’s day-based time values.

This approach avoids treating 30 minutes as 30 hours. If you instead need decimal hours from those two columns, add the hours to the minutes divided by 60. Choose the representation required by the rest of the workbook before building totals: duration values are convenient for time arithmetic, while decimal values are often easier to multiply by an hourly rate.

Common reasons an hours total looks wrong

The total is smaller than expected: Check whether the total uses a clock format such as h:mm instead of the elapsed format [h]:mm. A sum beyond 24 hours can otherwise appear to wrap.
An entry is not included: The cell may contain text rather than a numeric time value. Re-enter it as a time or convert the imported data to real Excel values before summing.
The result is a decimal or a large number: The calculation may be fine, but the result cell has a General, Number, or date/time format that does not match the intended output. Apply the correct display format.
Minutes seem to turn into hundredths: Clock minutes are base 60, but decimal hours are base 100. Convert minutes to a fraction of an hour by dividing by 60; do not read 7:30 as 7.30 hours.
Negative durations show as hashes or an unexpected value: The subtraction may produce a negative time, often because a shift crossed midnight or the dates are reversed. Use a proper date-time difference or an overnight calculation where appropriate.

Also check for unintended spaces, inconsistent input, or a range that stops before the last row. A quick way to verify a total is to add a few known entries manually and compare the result, then inspect the number format and the values included in the SUM range.

Choose the right format for the job

Use elapsed time when readers need to understand a duration at a glance: 31:30 is usually clearer than 31.5 for a cumulative work total. Use decimal hours when another calculation needs a numeric hour quantity. For example, a decimal total can be multiplied by a rate per hour, while an elapsed-time value should first be converted to decimal hours for that kind of calculation.

For a simple worksheet, one column of duration entries and a SUM total is enough. For a shift log, keep start and finish date-times in separate columns and calculate each elapsed duration before summing it. For payroll or billing, check the organization’s rounding rules separately: displaying a value to two decimal places does not necessarily round the stored value, and different policies may round each shift or the final total.

Frequently Asked Questions

How do I sum hours in Excel?

Use the SUM function on the cells containing numeric durations, such as =SUM(B2:B5) when the entries are in B2:B5. Format the result as [h]:mm if the total might be 24 hours or more.

Why does Excel show 7:30 instead of 31:30?

The cell is probably using a clock-style time format that resets after 24 hours. Change its custom format to [h]:mm; the brackets make Excel display cumulative hours.

How do I convert 7:30 to decimal hours?

If the duration is in A2, enter =A2*24 in another cell and format the result as Number or General. The result is 7.5 hours. The layout assumption is that A2 contains a genuine Excel time value, not text.

Can Excel add hours written as 7.5?

Yes, if 7.5 means seven and a half decimal hours, add those numeric values directly with SUM and keep the result in a Number format. If you mean 7 hours and 5 minutes, enter it as 7:05 instead; 7.5 is not another way to write 7:05.

Can I add hours from multiple days?

Yes. Sum the duration cells and use [h]:mm to show cumulative hours. When calculating the individual durations, include both the date and time if an interval can cross midnight or last longer than one day.

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.