Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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?? |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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?? |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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?? |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding Time | Excel Worksheet Functions | |||
Time log adding time from separate sheets | New Users to Excel | |||
Adding Time | Excel Worksheet Functions | |||
Adding Time | Excel Worksheet Functions | |||
Adding time to date-time formatted cell | Excel Discussion (Misc queries) |