Forum Discussion

Copper Contributor

Oct 02, 2022

Solved

Time in Excel

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

excel

JoeUser2004
Oct 05, 2022
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).

Jeenine

Copper Contributor

Oct 05, 2022

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.

JoeUser2004

Bronze Contributor

Oct 05, 2022

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

Jeenine
Copper Contributor
Oct 06, 2022
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,