sum time values in excel

February 24, 2012 at 07:31:53
Specs: Windows XP

i'm trying to sum up time values (in hours and minutes).
Ex: 14.48
Total sum should be 30.09. Sum formula is considering minutes as 100 minutes and the result is 29.69. Can someone assist with the correct formula.


See More: sum time values in excel

Report •

February 25, 2012 at 09:38:36
Try this:

Convert your numbers to Time:
14. 48 > 14:48
15.21 > 15:21

Notice all I did was to replace the Dot with a Colon
Now Excel knows it's dealing with TIME, not just numbers.

Next you will need to Format your total cell as: [H]:MM:SS
The brackets are required.
You will find the format under Custom

So your data should look like this:

1)  14:48
2)  15:21
4)   30:09:00  <= Formula =SUM(A1:A2), formatted as [H]:MM:SS


Report •

February 27, 2012 at 06:39:37
Thanks Mike, it works. However is there any format to display the total 30:09:00 as 30.09 (i.e: 30 hours and 09 minutes worked)


Report •

February 27, 2012 at 08:30:26
Maybe it's just me, but if I were looking at a time-related report and saw 30.09 listed, I wouldn't interpret that to mean 30 hours and 9 minutes.

I would interpret that to mean 30:05:24 since .09 of an hour is 5.4 minutes and .4 of a minute is 24 seconds.

Substituting a decimal point for a colon in a displayed time value could lead to confusion since not only isn't it a standard way to show time, but a decimal point has a specific meaning, i.e. a decimal fraction of a whole number.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

February 27, 2012 at 11:47:52
I would have to agree with DerbyDad as to substituting a decimal point for a colon.

You could reduce the length a bit, by using [H]:MM
to just display the Hours:Minutes as 30:09


Report •

Ask Question