How to sum time in Excel

SUM the times, then format the total as [h]:mm

The sheet is a freelancer's week: time spent per client, typed as hours and minutes (3:45, 4:20). Excel stores each one as a fraction of a day, so 6:00 is 0.25 and 3:45 is 0.15625. SUM adds those fractions like any other numbers, which is why the total itself is right: 1.576 days.

The format decides what you see. Set it once: select the total, press Ctrl+1, choose Number > Custom and type [h]:mm in the Type box. Use [h]:mm:ss if you track seconds. The format sticks to the cell, so you only do this once per total.

You can also add individual times with +, as in =C2+C3, which gives 8:05 for Monday. Both work; SUM is easier to extend when you add rows.

Why the total shows 13:50 instead of 37:50

This is the problem almost everyone searching "sum time in Excel" has. When you SUM time cells, Excel copies the h:mm format to the total. h:mm is a clock format: it shows the time of day, and a clock never goes past 23:59. 37:50 is one full day plus 13:50, so h:mm shows 13:50 and quietly hides the 24 hours.

Nothing is wrong with the formula or the data. Change the format to [h]:mm and the same cell shows 37:50. The brackets mean "keep counting hours past 24". [m]:ss does the same for minutes.

A quick test: if a total looks too small and is less than 24 hours, check the format before you check the data.

Decimal hours for invoices and pay

A time total cannot be multiplied by an hourly rate directly: 1.576 x 65 is about 102, not the 2,459 you are owed. Multiply by 24 first to turn days into hours, then by the rate in H2. Format the result as Number or Currency, not Time.

Round once, at the end. =ROUND(SUM(C2:C9)*24*H2,2) gives 2,459.17. Rounding the hours first, =ROUND(SUM(C2:C9)*24,2)*H2, uses 37.83 hours and gives 2,458.95. Over a year of invoices those cents add up and stop matching the timesheet, so pick one rule and use it everywhere.

To show a total as text such as "37 h 50 min", use =INT(SUM(C2:C9)*24)&" h "&MINUTE(SUM(C2:C9))&" min". HOUR() would return 13 here, for the same reason h:mm shows 13:50.

SUM returns 0: the times are text

Column D holds the same times exported from a time-tracking app as CSV. They look identical, but they are text, and SUM skips text completely, so the total is 0:00. Signs to look for: the times are left-aligned, =COUNT(D2:D9) returns 0, or a green triangle appears in the corner of each cell.

Two fixes. To leave the data alone, convert inside the formula: the double minus (--) forces each text time into a number and SUMPRODUCT adds them. To fix the data itself, select the column and choose Data > Text to Columns > Finish, which turns time-like text into real times. Then plain SUM works and so does every other formula that uses the column.

Note that + converts text but SUM does not: =D2+D3 returns 8:05 while =SUM(D2:D3) returns 0. That is why a hand-built total can disagree with SUM on the same cells.

Sum time by client or day with SUMIF

SUMIF and SUMIFS add times the same way SUM does, so the result needs the [h]:mm format too. Acme has four entries this week: 3:45, 6:15, 5:30 and 4:40.

Add more conditions with SUMIFS: =SUMIFS(C2:C9,B2:B9,"Acme",A2:A9,"Wed") returns Acme's 5:30 on Wednesday. Put the client name in a cell and refer to it instead of typing "Acme", so one formula filled down gives a total per client. If SUMIF returns 0 on times you can see, it is the text problem from the previous section.

Negative totals: hours short of a target

A flexitime or capacity sheet compares the week's total with a target. The target here is 40 hours, typed as a plain number in H1, so it is divided by 24 to match the time values. The week is 2 hours 10 minutes short, and that negative time is where Excel fails: a cell formatted as time shows ##### for any negative value.

TEXT cannot format it either: =TEXT(SUM(C2:C9)-H1/24,"[h]:mm") returns #VALUE!, because TEXT will not show a negative number as a time. So calculate the size of the gap with ABS, format that with TEXT, and add the minus sign yourself. LET names the difference once so the formula stays readable. The result is text, so use it for display only; for further maths, keep the number in decimal hours: =ROUND((SUM(C2:C9)-H1/24)*24,2) gives -2.17.

You may read that switching to the 1904 date system (File > Options > Advanced) shows negative times. It does, but it also moves every existing date in the workbook by 4 years and 1 day, so avoid it in any file that has dates. Typing the target as 40:00 into a cell also works: Excel stores it as a time of 40 hours, and then you leave out the /24.

Minutes and seconds: type 0:04:20, total with [m]:ss

Call lengths, lap times and video durations are minutes and seconds. If you type 4:20, Excel reads it as 4 hours 20 minutes, and the total is 60 times too big. Type the hours as well, 0:04:20, then format the cells as mm:ss so you do not see the zero.

For the total, use [m]:ss. These five calls add up to 89 minutes 30 seconds. With mm:ss the total shows 29:30, because the format resets every hour, the same trap as h:mm at 24 hours. h:mm:ss shows 1:29:30, which is also correct.

For average handling time or per-minute billing, convert to decimal minutes by multiplying by 1440, the number of minutes in a day: =SUM(F2:F6)*1440 gives 89.5.

Which method to pick

For a total you will read, =SUM() with the [h]:mm format (or [m]:ss for short durations). For anything you multiply by a rate or compare with a target in hours, the total times 24, rounded once at the end. If SUM returns 0 or too little, the times are text: SUMPRODUCT(--range) works around it, Text to Columns fixes it for good. For balances that can go negative, keep decimal hours for calculations and use the ABS and TEXT formula only for display.