Show Hrs:Min

[Tonso]

I have in cell S6 a formula that takes a value, and multiplies it by
0.0006, and the result is seconds, such as 135. I want to have another
cell that references the cell with the seconds (S6), and shows
minutes:seconds, so that the 135 in the example would result in 2:15.
I tried =Int(S6/60)&":"&mod(S6,60), but only get #VALUE. What is the
proper formula?

Thank you,

Tonso

 

[Rick Rothstein]

Use this formula...

=S6/86499

and format the cell as mm:ss

Rick Rothstein (MVP - Excel)




"Tonso" wrote in message

I have in cell S6 a formula that takes a value, and multiplies it by
0.0006, and the result is seconds, such as 135. I want to have another
cell that references the cell with the seconds (S6), and shows
minutes:seconds, so that the 135 in the example would result in 2:15.
I tried =Int(S6/60)&":"&mod(S6,60), but only get #VALUE. What is the
proper formula?

Thank you,

Tonso

 

[Rick Rothstein]

Use this formula...

=S6/86499

TYPO ALERT
------------------------
The formula I posted should have been this...

=S6/86400

Rick Rothstein (MVP - Excel)

 

[Tonso]

TYPO ALERT
------------------------
The formula I posted should have been this...

=S6/86400

Rick Rothstein (MVP - Excel)

Thanks Rick! that works great! Why, i have not idea. But it does.
Thanks again...

Tonso

 

[Clif McIrvin]

TYPO ALERT
------------------------
The formula I posted should have been this...

=S6/86400

Rick Rothstein (MVP - Excel)

Thanks Rick! that works great! Why, i have not idea. But it does.
Thanks again...

Tonso


------

Rick may post back with a more complete answer; but it works because of
the way Excel stores date/time values. The integer portion of a
date/time value is days (since 0 January 1900); the fraction is
hours/minutes/seconds.

24 hours * 60 minutes * 60 seconds = 86400

so, Rick's formula takes your count of seconds and converts it to
Excel's date/time data type internal format.

 

[Tonso]

Thanks Rick! that works great! Why, i have not idea. But it does.
Thanks again...

Tonso

------

Rick may post back with a more complete answer; but it works because of
the way Excel stores date/time values.  The integer portion of a
date/time value is days (since 0 January 1900); the fraction is
hours/minutes/seconds.

24 hours * 60 minutes * 60 seconds = 86400

so, Rick's formula takes your count of seconds and converts it to
Excel's date/time data type internal format.

That makes sense. Thanks Cliff!

Tonso