SOLVED

New Contributor

# Code Formatting

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 (New Contributor)
Solution

# Re: Code Formatting

Hi Jerry,

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

Regards

# Re: Code Formatting

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.

# Re: Code Formatting

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