# 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 15.21Total 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.Thanks,Ali

See More: sum time values in excel

#1
February 25, 2012 at 09:38:36
 Try this:Convert your numbers to Time: 14. 48 > 14:4815.21 > 15:21Notice all I did was to replace the Dot with a ColonNow Excel knows it's dealing with TIME, not just numbers.Next you will need to Format your total cell as: [H]:MM:SSThe brackets are required.You will find the format under CustomSo your data should look like this:``` A 1) 14:48 2) 15:21 3) 4) 30:09:00 <= Formula =SUM(A1:A2), formatted as [H]:MM:SS ```MIKEhttp://www.skeptic.com/

Report •

#2
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)Thanks

Report •

#3
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

#4
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:09MIKEhttp://www.skeptic.com/

Report •