Adding time

[smiley61799]

I am trying to add time and I am having some difficulties finding a way to
format my numbers to be on a time scale. Example:

John worked
Monday 8.02
Tuesday 8.0
Wednesday 8.54
Thursday 8.09
Friday 7.59

This would total to 41.04 hours for the week but if I try to add this on
Excel it is using the number scale of 100 instead of a time scale of 60 so I
am getting a total of 40.24. Is there any way to format this to add it
correctly??

 

[T. Valko]

Monday 8.02

Is 8.02 supposed to be 8 hours and 2 minutes?

If so, why don't you just enter those values as *time*:

8:02
8:00
8:54
8:09
7:59

=SUM(A1:A5)

Format as [h]:mm

Result = 41:04

Quick time/date entry:

http://www.cpearson.com/Excel/DateTimeEntry.htm

 

[Bernard Liengme]

Biff (T Valko) has given you the best solution. However, if you do not want
to reenter all the values, this formula works
=SUMPRODUCT(INT(B1:B5)*60+MOD(B1:B5,1)*100)/(60*24)
This is NOT an array formula in the sense that you do not need to use
CTRL+SHIFT+ENTER
Note we add hours and mins, then convert to a fraction of a day since that
is how Excel stores time
The cell should be given custom format {h}:mm ----the braces ensures the
Excel does not work with a 24 hour max.
best wishes

 

[Rajesh Mehmi]

You can try the following too


your time value is in column B
put the following formula in column C and copy it down
=((B3 -(TRUNC(B3))) *100/60 ) + TRUNC(B3)

then add column C and put the following formula to convert the total back to
hh.mm
=((C9-(TRUNC(C9)))*60/100)+TRUNC(C9)


--
Best regards

Rajesh Mehmi

(e-mail address removed)