Summing hours and minutes

**zebra2011** · 11-21-2011, 02:35 PM

I am trying to sum a series of times and despite formatting the numbers using the custom formant [h]:mm it won't sum beyond 99:59 (99 hours and 59 minutes). Everytime a summed time goes above 100 hours it reverts to a date and time format. Many of the times I am summing will go beyond 100hours and so I need it to display this accurately.

Why is it doing this and can I prevent it? Can I get excel to sum the times and display them properly?

Thanks for your help in advance.

**leroivt** · 11-21-2011, 02:46 PM

Summed time who goes beyond the 24 hours is counted , I think , in days. So how about if you format your cell in custom mode like "dd hh:mm" will give you e.g : 3 days and 15 hour and 13 minutes.

**daddylonglegs** · 11-21-2011, 03:21 PM

There shouldn't be any problem summing beyond 99:59. I can do that easily, e.g. if I put 50:00 in A1, 30:00 in A2 and 40:00 in A3 and in A4

=SUM(A1:A3)

then I get the result 120:00 as expected

Which formula are you using to sum - can you give a more specific example?

**zebra2011** · 11-22-2011, 09:23 AM

I was using the SUMIF function and for some reason the problem only applied to one cell. no matter how I reformatted the cell it would not display a number greater than 99:59. When I selected the cells to add together manually using the SUM function it displayed the correct answer - 100:01. Every other cell worked perfectly using the SUMIF function.

Sometimes I think excel just plays with us!