Adding time
 

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

Adding time
 

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


--
Biff
Microsoft Excel MVP


"smiley61799" wrote in message
...
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??

Adding time
 

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"smiley61799" wrote in message
...
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??

Adding time
 

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





"smiley61799" wrote in message
...
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??