dispay numbers as minutes and seconds in excel

[neilandko]

I am writing results for a cycling time trial in an Excel table. I want to
display each result as minutes and seconds eg 29:21 means 29 minutes and 21
seconds, not 29 minutes and 0.21 of a minute (base 60, not decimal).
OK, so I look in cell formatting and Excel help and am told to use the code
[mm]:ss. When I apply this, though, the cell treats the number as though it
were 29.21 days and then displays it as 42062:24 (minutes). Obviously this is
not what I want - so how do I get what I do want?
Thanks!

 

[Pete_UK]

You need to type it in as 0:29:21, or as 29:21.0, and then it will
display correctly as 29:21 meaning 29 minutes and 21 seconds.

If you just type 29:21 then Excel will take this as meaning 29 hours
and 21 minutes.

Hope this helps.

Pete

 

[David Biddulph]

Firstly, don't type the number in as 29.21; type it in as 29:21 (with a
colon, not a decimal point).
If you've stored the number as 29.21, changing the format will affect the
display, but not the stored value, which (as you say) is 29.21 days.
But 29:21 would be treated as 29 hours 21 minutes. For 29 minutes and 21
seconds you need to type in as 0:29:21 or 29:21.0 (or divide by 60 after
you've tyoed the number in).