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.

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

"mftr" wrote:

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.

Hi,

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

Mike

"mftr" wrote:

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.

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

and format as mm:ss

Regards,
Stefi

€žMike H€� ezt Ã*rta:

Hi,

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

Mike

"mftr" wrote:

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.

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" wrote:

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

and format as mm:ss

Regards,
Stefi

€žMike H€� ezt Ã*rta:

Hi,

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

Mike

"mftr" wrote:

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.

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:

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" wrote:

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

and format as mm:ss

Regards,
Stefi

€žMike H€� ezt Ã*rta:

Hi,

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

Mike

"mftr" wrote:

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.

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

mftr

"Mike H" wrote:

Hi,

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

Mike

"mftr" wrote:

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.

You are welcome! Thanks for the feedback!
Stefi

€žmftr€� ezt Ã*rta:

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

mftr

"Mike H" wrote:

Hi,

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

Mike

"mftr" wrote:

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.