i'm trying to sum up time values (in hours and minutes).

Ex: 14.48

15.21

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.Thanks,

Ali

Try this: Convert your numbers to Time:

14. 48 > 14:48

15.21 > 15:21Notice 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 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:SSMIKE

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

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.

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:09MIKE

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History