Time factor

[Debbie]

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.

 

[Jacob Skaria]

=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

 

[Debbie]

That one didn't work.
I entered: =SUM(D413)/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]

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

 

[David Biddulph]

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

 

[JoeU2004]

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(D110)/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(D110). (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(D110)/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 D111, each of which is 0.01 (or
0:01). 100*SUM(D111)/1440 with the Custom format [h]:mm (or simply
SUM(D111)) 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 -----