MrExcel Publishing
Your One Stop for Excel Tips & Solutions

seconds to minutes:seconds


Posted by jason on October 12, 1999 9:19 AM

i have a spreadsheet that i want to enter 10 seperate cells (E2:N2) of times in measured in seconds. i would like an excel formula to convert these cells to minutes:seconds. all the formulas and formatting i have tried don't seem to work. i would think this is a simple thing to do, but i haven't found it yet.

thanks,

Jason


Posted by Michael Parks on December 24, 1999 4:49 AM

Just divide the number of seconds by 86,400 (the number of seconds in a day) and format the cells with the custom time fomat mm:ss.
------------------------------------------------

Posted by Mark on March 29, 2000 11:09 AM

Follow up question/comment:
Going the other way is not exactly the opposite technique. Multiplying the input by 86400 DOES work, but formatting the INPUT cell as mm:ss requires the user to put 0:01:10 to get the result cell to read "70". You'd expect to type only 1:10 to get that result, but Excel will read that as 01:10:00.
Is there a way to apply an input mask that allows input as 1:10 to read as 1 minute, 10 secs?
Thanks y'all.
mb