Jun 25 2021 12:21 PM
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!
Jun 25 2021 12:38 PM
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.
Jun 25 2021 12:48 PM - edited Jun 25 2021 01:03 PM
@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.
Jun 25 2021 01:17 PM
SolutionAs 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
Jun 25 2021 04:06 PM
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!
Jun 25 2021 04:19 PM
Jun 25 2021 01:17 PM
SolutionAs 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