Adding hours
Posted by Mauricio on February 13, 2001 2:22 PM
How can I add hours and get the total in hour and not days.
Adding 20:00 hrs + 10:00 hrs give you "1/1/00 6:00"
What I am looking for is "30:00"
Posted by Mark W. on February 13, 2001 2:57 PM
If cell A1 contains 20:00 and cell A2 contains 10:00
then use:
{=TRUNC(SUM(A1:A2*1440)/60)&":"&TEXT(MOD(SUM(A1:A2*1440),60),"00")}
This is an array formula which must be entered using
Shift+Ctrl+Enter. The outer braces are supplied by
Excel -- not entered by you.
Posted by Celia on February 13, 2001 3:16 PM
If the two cells containing the hours are A1 & B1, Format C1 as [hh]:mm and enter =A1+B1
Celia
Posted by Mark W. on February 13, 2001 3:40 PM
Mauricio, I'm certain that Celia's solution is what
you're seeking. A couple of things you ought to
keep in mind.
1. Whenever a value is quoted in Excel (e.g.,
"30:00") it generally means it's a text string. So
if you want a numeric value it would be best not
to use quotation marks.
2. You stated that you were looking for a "total in
hour[s] not days". Be aware that although Celia's
solution is diplayed as hours and minutes it is stored
internally as days -- 1.25 (days) in the case of 30:00.
In fact all of Excel's date/time formats are stored as
days. 5:00 is stored as 0.25 days and can be converted
into minutes by multiplying it by 1440 (24 hours x 60
minutes).
Posted by Mark W. on February 13, 2001 3:43 PM
Typo correction
2. ... 6:00 is stored as 0.25 days ....
