Enter Time as mm:ss only

[Joe_Gandalf]

I want to be able to enter the elapsed time for music files for a radio show I produce. Right now I have to enter "0:mm:ss" (songs don't last longer than 18 minutes max). Since I often import similar data that contains only mm:ss, how can I make Excel understand that I do not want a time-of-day format, just minutes & seconds?

Using Win 10 & Excel (no idea what version; not 365)

Thanks, everyone!

[#2]

@Joe_Gandalf 

You need to format the cells / range or worksheet the way you want.

Steps:

Select Cells / Range or Entire Worksheet Right mousekey

Format cells ...

Format cells window Custom Enter

"[mm]: ss" (without goosefoot) or let the link show you the options.

Format numbers as dates or times

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

[#3]

@Joe_Gandalf....

Would it be acceptable to enter data in the form mm:ss.0?

Excel always interprets the form 12:34 as 12h 34.  But 12:34.0 is interpreted as 12m 34.0s.

(Note: The format of the cell before data entry does not change how data entry is interpreted.  It only changes the appearance of the result.)

And of course, you can enter non-zero fractional seconds, e.g. 12:34.567890123.  But Excel rounds to the 1/1000 second (millisecond); seconds with 3 decimal places.

In any case, you might want to choose a Custom format that differs from the default that Excel chooses for times that are entered in form 12:34.0.

[#4 · Accepted solution]

@Joe_Gandalf 

As variant you may enter (let say in cell A1) 12:34 which is actually 12 h 34 m, but in all calculations use =A1/60 which returns 12 m 34 s

[#5]

@Sergei Baklan 

Your suggestion might be my best bet (if it isn't possible to create a custom format that will do what I want!). At least your method will allow me to import data that is already in mm.ss format.

Thanks!

[#6]

mm:ss.0 would be a bit simpler than 0:mm:ss, but it won't allow me to import some existing data that is in mm:ss .