h:mm:ss formula

[Dick]

How would you calculate the total times if your times were entered as:
Example
Column C:C will have 5 for 5 minutes, .30 for 30 seconds, 15 for 15
minutes. And so on... I can't seem to figure out how to tell excel
that a decimal number is seconds and a whole number is minutes. Up to
59 minutes, then my sum total time would be h:mm:ss. Thanks so much
for your help!!!

 

[Ron Rosenfeld]

How would you calculate the total times if your times were entered as:
Example
Column C:C will have 5 for 5 minutes, .30 for 30 seconds, 15 for 15
minutes. And so on... I can't seem to figure out how to tell excel
that a decimal number is seconds and a whole number is minutes. Up to
59 minutes, then my sum total time would be h:mm:ss. Thanks so much
for your help!!!

Must you enter your time values as a decimal? You might not have to do anything if you were to enter 5 1/2 minutes as 0:5:30.

If you MUST use m.s (e.g. 5.30) you do need to remember to enter a leading zero for seconds less than 10; e.g. 5 min 5 sec --> 5.05 and you must accept the fact that 5.5 will be entering 5 min 50 sec.

With that in mind, you can convert your entry to a value that excel will understand as a time value with the following formula:

A1: 5.30 (5 min 30 sec)

B1: =(INT(A1)+MOD(A1,1)*100/60)/(24*60) and custom format the result as [h]:mm:ss

Excel stores times as a fraction of a day.

The integer part of your number represents minutes and there are 24*60 or 1440 minutes in a day.
The decimal part represents seconds.

You can sum column B to get your summation.

 

[Rick Rothstein]

B1: =(INT(A1)+MOD(A1,1)*100/60)/(24*60)

[Sorry for this, but it's Sunday and I'm bored<g>]

You could also use this formula in B1...

=--SUBSTITUTE(TEXT(A6,"0\:00.00"),".",":")

Rick Rothstein (MVP - Excel)

 

[joeu2004]

How would you calculate the total times if your times
were entered as: Example
Column C:C will have 5 for 5 minutes, .30 for 30 seconds,
15 for 15 minutes.

What is 5 min 2 sec: 5.02 or 5.2?

I hope it is not the latter; cannot distinguish that from 5 min 20
sec. Assuming 5.02....

And so on... I can't seem to figure out
how to tell excel that a decimal number is seconds and
a whole number is minutes. Up to 59 minutes, then my sum
total time would be h:mm:ss.

=SUMPRODUCT(--("0:"&INT(C1:C100)&":"&ROUND(100*MOD(C1:C100,1),0)))

formatted as Custom [h]:mm:ss. The double-negative (any arithmetic
operation) converts text to numeric.

Note that the result is Excel time, such that 1 hour is 1/24, 1 min is
1/1440, and 1 sec is 1/86400.

PS: It would be better to convert the bogus time representation by
putting the following formula in D1 and copying down:

=--("0:"&INT(C1:C100)&":"&ROUND(100*MOD(C1:C100,1),0))

 

[joeu2004]

=--SUBSTITUTE(TEXT(A6,"0\:00.00"),".",":")

That was my first impression, too. But when I realized that it would
not work for minutes > 99 (and I'm surprised that it works for minutes

59), I decided to abandon it. It is unclear whether or not that

would be a problem for Dick.

 

[Rick Rothstein]

That was my first impression, too. But when I

realized that it would not work for minutes > 99

Theoretically, as I understand it, numbers greater than 59 can't be produced
unless the user makes a typo. Unless, of course, I have made a bad
assumption. I was assuming the OP would use Ron's or my formula next to each
cell with one of his "funny" time values in it in order to produce a real
time... and those real times would be summed up and have the [h]:mm:nn
format applied to it.

Rick Rothstein (MVP - Excel)