Oct 01 2022 11:03 PM
Oct 01 2022 11:03 PM
Has anyone an idea how I can change the time format for cells. I want to put in Minutes and seconds on my discography but cannot find an easy way to do this. Format only allows Hours minutes and seconds. I know I can get £.p formatting so just adding the numbers gives the right amount and would love to do the same with mins:secs.
I am not technically minded so a simple explanation would be good, if there is one. Thanks in advance.
Oct 02 2022 04:39 AM
Select the cells with the times.
On the Home tab of the ribbon, click the Number Format drop down and select More Number Formats...
Select Custom in the Category list.
Enter the custom format m:ss in the Type box. If the number of minutes could be 60 or more, use [m]:ss instead.
Oct 02 2022 07:50 AM - edited Oct 02 2022 07:53 AM
@Jeenine wrote: ``how I can change the time format for cells. I want to put in Minutes and seconds``
Those are two separate questions. It is unclear which you are asking, or both.
When we type x:y, it is interpreted as x hours y minutes, no matter how the cell is formatted (except for Text).
Type x:y.0 or 0:x:y if you want it interpreted as x minutes y seconds, again no matter how the cell is formatted. We can also type x:y.fff and 0:x:y.fff to include fractional seconds up to fff milliseconds.
(Note: "fff" represents the actual number of milliseconds that you want, e.g. 123, not the literally the characters fff.)
After time is entered properly, we can change its appearance by formatting the cell appropriately.
There are many ways to do that. One way is: right-click the cell(s), click Format Cells > Number > Custom, and enter the format that you want, perhaps [m]:ss or [m]:ss.000 .
I recommend [m] instead of simply m so that the correct number of minutes will be displayed, even if it is 60 or more.
Oct 04 2022 05:45 PM
Oct 04 2022 10:38 PMSolution
I'm sorry that I was not clear.
If you want to enter 3:55 and have it interpreted as 3m 55s, you must enter either 3:55.0 or 0:3:55.
Alternatively, you can enter 3:55 in one cell, say A1 (Excel interprets that as 3h 55m), and enter a formula like =A1/60 in another cell to calculate 3m 55.
Alternatively, you can create an event macro that changes the value in A1 (3h 55m) to 3m 55s by dividing A1 by 60.
I don't really recommend either of the latter two. KISS: just enter 3:55.0 or 0:3:55. Why not?
In all cases, you might also need to format the cell whose value is 3m 55 as Custom [m]:ss.
The reason is: Excel likes to do us "favors" and guess what format that we want. So we have to override its guess (sigh).
Oct 05 2022 06:54 PM