Which format to use for minutes and seconds?

I have several fields in a spreadsheet that I need to use to enter a length of time measured in mins and seconds. In the past, I've used the [h]:mm:ss format for entering hours, minutes and seconds (ie 129:15:35 = 129 hours, 15mins and 35 seconds). It doesn't seem to work for just minutes and seconds though.

Is there a format i can use that will let me enter 1:21 and treat it as 1min, 21seconds?

Originally Posted by guilbj2

Is there a format i can use that will let me enter 1:21 and treat it as 1min, 21seconds?

In short, no. You need to enter as 0:1:21. If you enter 1:21 excel will always take this to be 1 hour 21 minutes.

You could, however, convert afterwards with a formula in another cell, e.g. =A1/60 or to convert a range of such values in situ...

Put 60 in an empty cell, copy it, select your range of times and use Edit > Paste Special > "divide". Now reformat to mm:ss