MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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