How to Add Hours and Minutes in Excel (Totals Over 24 Hours)

Excel adds hours and minutes as easily as any other numbers, but only if you know how it stores time. Most wrong totals (17:30 when you expected 41:30, a sum of 0:00, a mysterious 44:00) come from formatting or from mixing two kinds of hour values, not from the SUM function itself. This guide gives the formula for each situation and the format that makes the result readable. If you only need a quick answer without a spreadsheet, the hours and minutes calculator adds durations for you.

How Excel stores hours and minutes

When you type 8:30 into a cell, Excel stores a fraction of a day: 8 hours 30 minutes is 8.5 ÷ 24 = 0.354167. One hour is 1/24 (about 0.041667) and one minute is 1/1440. Because time values are ordinary numbers, you can add and subtract them with +, - and SUM. What changes is how the result is displayed, and that is controlled entirely by the cell’s number format.

Two consequences matter for everything below:

• A total of 24 hours or more is a value of 1 or more. Formats like h:mm show only the part left over after the whole days.
• A plain number such as 1.5 means 1.5 days when you add it to a time value, not 1.5 hours.

Sum a column of hours and minutes

Suppose column B holds the hours worked each day as h:mm durations:

The total in B7 is a plain sum:

=SUM(B2:B6)

Excel usually gives the result the h:mm format automatically, which displays 17:30: the total minus one whole day. To fix it, select B7, press Ctrl+1 (Cmd+1 on a Mac) to open Format Cells, choose Custom, and type [h]:mm in the Type box. The square brackets tell Excel to show elapsed hours instead of the hour of the day, so the cell now shows 41:30. Use [h]:mm:ss if your entries include seconds.

In Google Sheets, choose Format → Number → Duration, which shows 41:30:00. For a display without seconds, use Format → Number → Custom number format and enter [h]:mm.

Apply [h]:mm to every cell that can reach 24 hours: weekly totals, pay-period totals and running balances. Daily entries are usually fine as h:mm.

If your hours come from clock-in and clock-out times rather than typed durations, work out each day first, as shown in calculate hours worked in Excel, then sum the daily results the same way.

Add minutes or hours to a clock time

To find an end time, add a duration to a start time. One minute is 1/1440 of a day, so divide minutes by 1440 before adding them:

=A2+B2/1440
=A2+TIME(0,B2,0)

Both formulas return the same results here. Format column C as h:mm AM/PM. The last row crosses midnight: the stored value is 1.03125 (one day plus 0:45), and the clock format shows 12:45 AM. TIME has two limits worth knowing. It truncates fractional arguments, so TIME(0,7.5,0) adds only 7 minutes, and its result wraps after 24 hours (minute arguments above 32,767 return an error). The division method has neither problem.

For whole or decimal hours, divide by 24 instead. If A2 is 7:00 AM and B2 is 8.5:

=A2+B2/24

The result is 3:30 PM. The add time calculator does the same thing if you want to double-check a single result.

Add hours and minutes kept in separate columns

Some timesheets record hours in one column and minutes in another. You can turn each row into a time value with =TIME(B2,C2,0), or total the columns directly and carry the extra minutes into hours:

=SUM(B2:B4)+INT(SUM(C2:C4)/60)
=MOD(SUM(C2:C4),60)

The minutes add up to 115, which is 1 hour 55 minutes, so the formulas return 8 and 55: a total of 8 hours 55 minutes. To get a single time value instead, use =SUM(B2:B4)/24+SUM(C2:C4)/1440 formatted as [h]:mm, which shows 8:55.

Total decimal hours and convert the result

If your hours are decimals (2.5 instead of 2:30), they are plain numbers and add normally:

=SUM(B2:B5)
=SUM(B2:B5)/24

The first formula returns 8.1 (format it as General or 0.00). The second converts the total to a time value; formatted as [h]:mm it shows 8:06, because 0.1 hour is 6 minutes. More options are covered in convert decimal hours to time in Excel.

Don’t mix the two kinds of values in one formula. If A2 holds 8:00 and B2 holds 1.5, =A2+B2 adds a day and a half and shows 44:00 in [h]:mm. Write =A2+B2/24 to get 9:30.

Add times that are stored as text

Times imported from a CSV file, a web page or another system are often text that only looks like time. The signs: entries are left-aligned, SUM returns 0:00, and changing the format does nothing. You can total them without fixing the source:

=SUMPRODUCT(--B2:B6)

The double minus converts each text entry such as “8:30” into a number. It returns #VALUE! if any cell holds text that isn’t a time, including an empty string returned by another formula. To fix the column permanently, select it and use Data → Text to Columns → Finish, or convert each cell with =TIMEVALUE(B2) in a helper column. Then format the total as [h]:mm.

Total hours by employee or project with SUMIF

SUMIF and SUMIFS work on time values exactly as they do on other numbers:

=SUMIF($A$2:$A$6,"Ana",$C$2:$C$6)
=SUMIFS($C$2:$C$6,$A$2:$A$6,"Ana",$B$2:$B$6,"Alpha")

The first formula returns 14:05 for Ana (Ben’s total is 8:35). The second returns 10:20, Ana’s hours on project Alpha only. Format both as [h]:mm. For decimal hours, multiply by 24: Ana’s 14:05 becomes 14.08 when rounded to two decimals. In a summary table, replace “Ana” with a cell reference such as E2 so one formula can be filled down for every employee.

Subtract durations and handle negative results

Subtraction works the same way: =B2-A2. The trouble starts when the result is negative. If A2 is a scheduled 40:00 and B2 is 37:45 actually worked, the difference is -2:15, and in Excel’s default 1900 date system a negative time displays as #####. Three workarounds:

=ABS(B2-A2)
=(B2-A2)*24
=IF(B2<A2,"-","")&TEXT(ABS(B2-A2),"[h]:mm")

• ABS shows 2:15 but loses the sign.
• Multiplying by 24 returns -2.25 decimal hours; format it as 0.00, not as a time.
• The TEXT version shows -2:15, but the result is text, so you can’t add it to other cells.

You can also switch the workbook to the 1904 date system (File → Options → Advanced → Use 1904 date system; on a Mac, Excel → Preferences → Calculation), which displays negative times. Be careful: it shifts every date already in the workbook by 1,462 days, so use it only in sheets without dates. Google Sheets shows a negative duration such as -2:15:00 when the cell uses the Duration format. For hours beyond a 40-hour threshold, see calculate overtime in Excel.

Common errors and fixes

When the total is right but payroll needs it as a number like 41.5, the last step is converting it, which is covered in convert time to decimal in Excel.