Time Format Conversion of a Number (sec)

[Eric Deneault]

I have a calculated cell that returns a number which is equivalent to the number of seconds. I wish to format the cells output, without changing that calculation in the cell and have the cell's output in mm.ss just as one would see on a digital stopwatch.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
75 would then read 1.15 (one minute 15 seconds);<o></o>
<o></o>
33 would then read 0.33 (33 seconds)<o></o>
<o></o>
I truly hope that this is not embarrassingly simple but I need to find the answer and will be grateful for any assistance.
<o></o>
Thank You

 

[Jon von der Heyden]

Hello Eric and welcome to MrExcel.com

Try:
=A1/1440

Where A1 houses the value (e.g. 75)

Format the result as time.

 

[barry houdini]

I don't believe you can do that with formatting alone, without changing the calculation. If you have 75 you can convert to 1:15 by dividing by 86400 [the number of seconds in a day] so you could use

=(your formula)/86400

custom format cell as m.ss

 

[Jon von der Heyden]

Ooops, I converted it to hh:mm. Ignore my suggestion, Barry's is spot on!

 

[Eric Deneault]

Thank You Jon, this suggestion works well in my spreadsheet!

 

[Eric Deneault]

Correction thank you Barry & Jon - the division by 86400 and reformating to mm.ss works