SOLVED

Code Formatting

Copper Contributor

I'm developing a spreadsheet in Excel 2016 (Office 365, Version 16.0.7167.2055). I want to find out how to convert an number in a cell that represent a quantity in "seconds" that I want to convert to display as "minutes : seconds" but don't know the custom Number Code to make that happen. For example, the number in the cell is 65.6631951100942 and I want to have it display as 01:06 (one minute : 6 seconds). Using Excel Help, it says to do that I should use the Custom Number Code [mm]:ss, but when I do that it returns 94555:00 (which I’ve determined this represents the number of seconds in ~65.66 days, i.e. 65.66d*24hr*60min). I’ve tried other custom codes without success.

3 Replies
best response confirmed by Jerry Bodine (Copper Contributor)
Solution

Hi Jerry,

 

Please try this formula:

=TEXT(TIME(0,0,ROUND(A1,0)),"[mm]:ss")

To Seconds.png

 

Regards

Thank you very much, Haytham, that works very well. Although I have no idea how you came up with the formula. Obviously, I'm not very skilled with Excel yet.

Keep learning and with practice, and you will become like me!

 

If you want a strong Excel foundation for free, I would advise you to follow this guy.
I learned a lot from him!

 

Regards

1 best response

Accepted Solutions
best response confirmed by Jerry Bodine (Copper Contributor)
Solution

Hi Jerry,

 

Please try this formula:

=TEXT(TIME(0,0,ROUND(A1,0)),"[mm]:ss")

To Seconds.png

 

Regards

View solution in original post