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.

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.

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.

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.

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.

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.

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.

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.