SOLVED

Time in Excel

Occasional Contributor

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.

5 Replies

@Jeenine 

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.

Click OK.

S1801.png

@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.

Thanks for the info. I'm sorry if I am not clear. What I am wanting to do is list my albums giving an entry for each song with the time of the song which is in minutes and seconds i.e 3:55. I have tried the custom format as suggested above but it will still not allow me to input the figures and have the time automatically change it to m:ss as I can do with £.p That is when I format for £.pp I can input 3.55 and it will automatically fill cell as £3.55. This is what I want to do with the times of the tracks. when I enter the figures after the custom format it either reads 0:00:00 or a number which is not the number I input 3:50:50. I am doing something wrong but cannot see what it is.
best response confirmed by Jeenine (Occasional Contributor)
Solution

@Jeenine 

 

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).

Thanks for that. I didn't realise having to put the colon in was an important bit of the deal. Duh!! Bit of a thicko where tech is involved. Life is so much easier now,