Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time factor
I'm sure I'm overlooking something simple...
Please help: I'm adding time differences of: 0.02 (Minutes) 0.09 0.02 0.99 0.20 My total minutes equal: 132 I want to convert this number to the number of hours minutes which should be: 2:12 I can't seem to wrap my brain around this to find the answer. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time factor
Hi,
Here is the basic idea: For hours, assuming the total time 132 is in B1 =INT(B1/60) For minutes: =60*MOD(B1/60,1) you can combine these as needed. One problem I see it your notation for hour as minute: 0.02 is not Excel's notation for 2 minutes, to work with time you should enter them as 0:2:0. Also you show use the times 0.02 and then show us the total as 132? -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Debbie" wrote: I'm sure I'm overlooking something simple... Please help: I'm adding time differences of: 0.02 (Minutes) 0.09 0.02 0.99 0.20 My total minutes equal: 132 I want to convert this number to the number of hours minutes which should be: 2:12 I can't seem to wrap my brain around this to find the answer. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time factor
Debbie,
Use =SUM(cells)/1440 and format that cell as time (hh:mm:ss) 1440 is the number of minutes in a day (24*60), which converts the sum of minutes to fractions of a day (a value between 0 and 1), which is how Excel does timevalues. HTH, Bernie MS Excel MVP "Debbie" wrote in message ... I'm sure I'm overlooking something simple... Please help: I'm adding time differences of: 0.02 (Minutes) 0.09 0.02 0.99 0.20 My total minutes equal: 132 I want to convert this number to the number of hours minutes which should be: 2:12 I can't seem to wrap my brain around this to find the answer. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time factor
=SUM(A1:A10)/1440 and format to time..[h]:mm:ss
With your example It comes to 0:01:19 If this post helps click Yes --------------- Jacob Skaria "Debbie" wrote: I'm sure I'm overlooking something simple... Please help: I'm adding time differences of: 0.02 (Minutes) 0.09 0.02 0.99 0.20 My total minutes equal: 132 I want to convert this number to the number of hours minutes which should be: 2:12 I can't seem to wrap my brain around this to find the answer. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time factor
That one didn't work.
I entered: =SUM(D4:D13)/1440 It totaled my time as 2 min 50 sec. Should be 4 hrs 44 min 00:02:50 0.10 0.34 0.13 0.49 0.24 0.11 0.86 0.20 0.24 0.13 "Bernie Deitrick" wrote: Debbie, Use =SUM(cells)/1440 and format that cell as time (hh:mm:ss) 1440 is the number of minutes in a day (24*60), which converts the sum of minutes to fractions of a day (a value between 0 and 1), which is how Excel does timevalues. HTH, Bernie MS Excel MVP "Debbie" wrote in message ... I'm sure I'm overlooking something simple... Please help: I'm adding time differences of: 0.02 (Minutes) 0.09 0.02 0.99 0.20 My total minutes equal: 132 I want to convert this number to the number of hours minutes which should be: 2:12 I can't seem to wrap my brain around this to find the answer. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time factor
Debbie,
I thought that the times were decimal minutes (values less than one minute) but are actually whole minutes after the decimal place. Try =100*SUM(A1:A10)/1440 formatted as time - I got 4:44 bases on your values. HTH, Bernie MS Excel MVP "Debbie" wrote in message ... That one didn't work. I entered: =SUM(D4:D13)/1440 It totaled my time as 2 min 50 sec. Should be 4 hrs 44 min 00:02:50 0.10 0.34 0.13 0.49 0.24 0.11 0.86 0.20 0.24 0.13 "Bernie Deitrick" wrote: Debbie, Use =SUM(cells)/1440 and format that cell as time (hh:mm:ss) 1440 is the number of minutes in a day (24*60), which converts the sum of minutes to fractions of a day (a value between 0 and 1), which is how Excel does timevalues. HTH, Bernie MS Excel MVP "Debbie" wrote in message ... I'm sure I'm overlooking something simple... Please help: I'm adding time differences of: 0.02 (Minutes) 0.09 0.02 0.99 0.20 My total minutes equal: 132 I want to convert this number to the number of hours minutes which should be: 2:12 I can't seem to wrap my brain around this to find the answer. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time factor
For reasons which aren't apparent, you have entered your times in
one-hundredths of a minute. To get the number of minutes you therefore need =SUM(A1:A5)*100 To convert to Excel time, use =SUM(A1:A5)*100/1440 and format as [h]:mm -- David Biddulph "Debbie" wrote in message ... I'm sure I'm overlooking something simple... Please help: I'm adding time differences of: 0.02 (Minutes) 0.09 0.02 0.99 0.20 My total minutes equal: 132 I want to convert this number to the number of hours minutes which should be: 2:12 I can't seem to wrap my brain around this to find the answer. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time factor
"Debbie" wrote:
0.86 Apparently that means 86 minutes. What would 101 minutes look like: 1.01, 1.41 (!) or 0.101 (!!)? If 0.101, 100*SUM(D1:D10)/1440 will not work. I would suggest that you dispense with this unusual representation of time and convert it to the well-understood form h:mm. Then you can compute simply SUM(D1:D10). (But if you use the 0.101 form (!!), that will complicate the conversion.) In any case, the sum of time values might yield suprising results because of numerical abberations due to the way that Excel (and most applications) represents numbers with decimal fractions. To avoid that, you might consider using --TEXT(100*SUM(D1:D10)/1440,"[h]:mm") with the Custom format [h]:mm.. Alternatively, if you convert to the h:mm form, use --TEXT(SUM(D1:10),"[h]:mm"). (Note: the double-minus "--" is not a typo.) That will ensure that the internal representation of the displayed result matches exactly a constant or result from another formula that has the same displayed appearance (assuming you use the same --TEXT() trick in other time formulas.) For example, consider 11 time values in D1:D11, each of which is 0.01 (or 0:01). 100*SUM(D1:D11)/1440 with the Custom format [h]:mm (or simply SUM(D1:D11)) in A1 does not exactly match 0:11 in A2, if you look at the internal representation. (The displayed appearance will be the same.) Consequently, 60*(A1-A2) results in "####" when using 0.01, and 60*(A2-A1) results in "####" when using 0:11, which indicates negative time (!). ----- original message ----- "Debbie" wrote in message ... That one didn't work. I entered: =SUM(D4:D13)/1440 It totaled my time as 2 min 50 sec. Should be 4 hrs 44 min 00:02:50 0.10 0.34 0.13 0.49 0.24 0.11 0.86 0.20 0.24 0.13 "Bernie Deitrick" wrote: Debbie, Use =SUM(cells)/1440 and format that cell as time (hh:mm:ss) 1440 is the number of minutes in a day (24*60), which converts the sum of minutes to fractions of a day (a value between 0 and 1), which is how Excel does timevalues. HTH, Bernie MS Excel MVP "Debbie" wrote in message ... I'm sure I'm overlooking something simple... Please help: I'm adding time differences of: 0.02 (Minutes) 0.09 0.02 0.99 0.20 My total minutes equal: 132 I want to convert this number to the number of hours minutes which should be: 2:12 I can't seem to wrap my brain around this to find the answer. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Highest common factor | Excel Discussion (Misc queries) | |||
Factor Analysis | Excel Worksheet Functions | |||
IF FACTOR? | Excel Discussion (Misc queries) | |||
Growth Factor | Excel Discussion (Misc queries) | |||
PRIME FACTOR | Excel Worksheet Functions |