Forum Discussion
Time in Excel
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).
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.