Time format conversion

[mftr]

How can I convert a three digit seconds figure to minutes and seconds in a
mm:ss format (i.e. 162 seconds as 2:42)? I can get the right raw number by
dividing by 1440, but that seems to produce an h:mm format, albeit with the
right digits.

 

[EricBB]

=(162-MOD(162,60))/60 & ":" & MOD(162,60)

 

[Mike H]

Hi,

divide by 86400 (Seconds in a day) and format as mm:ss

Mike

 

[Stefi]

Another solution:
=TIME(0,0,162)

and format as mm:ss

Regards,
Stefi

„Mike H†ezt írta:

 

[Mike H]

Stefi,

That's fine for small numbers but falls over at 32768 seconds or 9h 6m 8s.
(2^15 or 111111111111111). Excel uses 15 bits for storing time which is a
maximum of 32768 numbers. Because time uses 0 then 32767 is the maximum value
accepted.

Mike

 

[Stefi]

Thanks Mike for the warning, you are right as far as theoretical base is
concerned. I think that in practice Time function still can be of good use,
because I can hardly imagine data given in seconds exceeding 32767 seconds.
Nonetheless, user has to be aware of this constraint and decide if his
application can tolerate it or not!

Regards,
Stefi


„Mike H†ezt írta:

 

[mftr]

Thank you EricBB, Mike H and Stefi for your quick responses; each of your
suggestions made it work like a charm.

mftr

 

[Stefi]

You are welcome! Thanks for the feedback!
Stefi

„mftr†ezt írta: