Hours and Minutes Sum

[problematical]

How do i calculate a total of 4 columns of hours and minutes? Not
"time"
For example, im keeping track of the amount of time i spend on 4
seperate projects. if i spend:
3 hours on the first,
2 and 1/2 on the second,
4 and 45 minutes on the third and
2 and 50 minutes on the fourth.
If i lay it out as a decimal (2), then they will read 3, 2.30, 4.45,
2.50 which calculates to 12.25.
in actuality it calculates to 13 hours and 5 minutes because hours
increase every 60 minutes. Its not 12 hours and 25 minutes.
How do i rectify this problem so the total is correct?

 

[Bob Greenblatt]

How do i calculate a total of 4 columns of hours and minutes? Not
"time"
For example, im keeping track of the amount of time i spend on 4
seperate projects. if i spend:
3 hours on the first,
2 and 1/2 on the second,
4 and 45 minutes on the third and
2 and 50 minutes on the fourth.
If i lay it out as a decimal (2), then they will read 3, 2.30, 4.45,
2.50 which calculates to 12.25.
in actuality it calculates to 13 hours and 5 minutes because hours
increase every 60 minutes. Its not 12 hours and 25 minutes.
How do i rectify this problem so the total is correct?

Well, you can't really blame Excel for not understanding that when you enter
4.45 you really mean 4 hours and 45 minutes. You should enter times as 4:45.
You'll probably have to multiply the total by 24 to get the result you want.
Look at this for more information on how to manipulate times in Excel:

http://www.cpearson.com/excel/datearith.htm#AddingTimes

 

[JE McGimpsey]

How do i calculate a total of 4 columns of hours and minutes? Not
"time"
For example, im keeping track of the amount of time i spend on 4
seperate projects. if i spend:
3 hours on the first,
2 and 1/2 on the second,
4 and 45 minutes on the third and
2 and 50 minutes on the fourth.
If i lay it out as a decimal (2), then they will read 3, 2.30, 4.45,
2.50 which calculates to 12.25.
in actuality it calculates to 13 hours and 5 minutes because hours
increase every 60 minutes. Its not 12 hours and 25 minutes.
How do i rectify this problem so the total is correct?

The easiest way is to use actual XL times (e.g., 3:00, 2:30, 4:45,
2:50), which you can then simply sum:

=SUM(A120)


If you insist in keeping your times in decimal form, here's one way:

=ROUND(SUMPRODUCT(INT(A120)) + INT(SUMPRODUCT(MOD(A120, 1))/0.6)
+ MOD(SUMPRODUCT(MOD(A120, 1))/0.6, 1) * 0.6, 2)

 

[problematical]

The easiest way is to use actual XL times (e.g., 3:00, 2:30, 4:45,
2:50), which you can then simply sum:

=SUM(A120)


If you insist in keeping your times in decimal form, here's one way:

=ROUND(SUMPRODUCT(INT(A120)) + INT(SUMPRODUCT(MOD(A120, 1))/0.6)
+ MOD(SUMPRODUCT(MOD(A120, 1))/0.6, 1) * 0.6, 2)

 

[problematical]

Thanks alot for the information. But what if i spend over 117 hours on
a project ? it has to be decimal form then. (117.45) 117 hours 45
minutes. I entered the formula you gave and it does not work. the
columns i want to calculate are C, D, E and F and they are all in
decimal form now. Thanks for your help.

 

[JE McGimpsey]

Thanks alot for the information. But what if i spend over 117 hours on
a project ? it has to be decimal form then. (117.45) 117 hours 45
minutes. I entered the formula you gave and it does not work. the
columns i want to calculate are C, D, E and F and they are all in
decimal form now. Thanks for your help.

I missed a set of parens. Should have been:

=ROUND(SUMPRODUCT(INT(A120)) + (INT(SUMPRODUCT(MOD(A120,
1))/0.6) + MOD(SUMPRODUCT(MOD(A120, 1))/0.6, 1)) * 0.6, 2)