Convert seconds to mm:ss

[Larry]

I have a worksheet that shows avg. elapsed time in seconds
as a numerical value (ex. 507.84275 is 507 seconds). I
need to convert this to a mm:ss format (08:27). Any help
would be appreciated.

 

[J.E. McGimpsey]

XL stores times in fractional days (e.g., 3:00 = 0.125), so to
convert seconds to time:

A1: 507.84275
A2: =INT(A1)/(24*60*60)

formatted as mm:ss

or, a bit more efficiently:

A2: =INT(A1)/86400

The INT is necessary because the mm:ss display will round to the
nearest second.

 

[Earl Kiosterud]

Larry,

J.E. has presumed you want the fractions of a second truncated, and probably
correctly, as you've indicated you want mm:ss formatting. If it turns out
you want to keep the fractional seconds, use:

=A1/(24*60*60)
and format:
mm:ss.000

Or if you want it rounded to the nearest second:
mm:ss

Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------