Time Conversion

[Psych-O-Delic Voodoo Thunder Pig]

Hello, I want to convert a numeric value such as 46 or 9243, which
represents seconds, to minutes and seconds in the format mm:ss I could not
find a built-in function to do this. The numeric value is in a column that
is ASCII data imported from another system.

The closest I could find is =CONVERT(A1,"sec","mn") but it gives the
fractions of a minute in decicimal value rather than seconds (assume the
date is in A1). I tried modifying it to =CONVERT(A1,"sec","mn:sec") but it
doesn't work.

I finally jury-rigged this function together:
=QUOTIENT(A1,60)&":"&TEXT(MOD(A9160),"00") which works but seems rather
kludgy. Am I missing a built in function to do what I want?

Thanks,

jp

 

[Psych-O-Delic Voodoo Thunder Pig]

Oooops, the function that works is
=QUOTIENT(A1,60)&":"&TEXT(MOD(A1,60),"00") sorry about the typo.

 

[Myrna Larson]

Excel stores a time as a fraction of a day. A day has 24*60*60 = 86,400 seconds. Just divide the
numbers by 86400 and format appropriately.

 

[Myrna Larson]

Or leave the formula alone (so it will calculate faster) and add a cell comment like

86,400 seconds in a day

 

[Sandy Mann]

or hide the comment in the formula as in:

=A1/86400+N("86,400 seconds in a day")