Adding time

  • Thread starter Thread starter smiley61799
  • Start date Start date
S

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??
 
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
 
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)
 
Back
Top