Time Conversion

  • Thread starter Thread starter Psych-O-Delic Voodoo Thunder Pig
  • Start date Start date
P

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
 
Oooops, the function that works is
=QUOTIENT(A1,60)&":"&TEXT(MOD(A1,60),"00") sorry about the typo.
 
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.
 
Or leave the formula alone (so it will calculate faster) and add a cell comment like

86,400 seconds in a day
 
Back
Top