Minutes & Seconds in Excel

Topic

New Reply

How do you format a cell so that the time you enter in reflected as hours and seconds?

I’ve tried by formatting the cell itself by picking hh:mm from the special category in the formatting section. However, whenever we enter in a time “08:24” for 8 min 24 secs, it always assumes it is 08:24 AM.

When I enter it in the long way (i.e. 00:08:24) and click on that cell, it says 12:08:24 AM. Any way to JUST show the minutes and seconds?

THANKS!

Reply | Quote

Replies

Try: format >> Cells >> Custom and choose either :mm:ss or mm:ss and enter your data as e.g. 00:08:24

Reply | Quote

Hans: On what version of Excel does :mm:ss work? My Excel 97 rejects that with a message saying that it could use the number format specified.

Also, the format mm:ss will only work for times less than 1 hour. If you enter 1:08:24, that format will display 8:24, not 68:24.

Reply | Quote

Sorry, this is Excel 2000. I am also sometimes confused with languages. I am using an English version of Excel but my Windows settings are Belgian. Could it be that you need to change to [h]?
This formatting will never display times like 68:24, but rather 01:08:24. The mm:ss format indeed ignores the hours. The best choice is your suggestion: [mm]:ss.

Reply | Quote

If you use the custom format ’00”:”00′ you can enter the time of 11 minutes, 20 seconds as 1120 and it will display correctly. This avoids you having to always key a leading ‘0:’ for the hours, but involves a bit more work if you need to sum the minutes and seconds.

Reply | Quote

You will need to create a custom format [mm]:ss. The brackets are typed into the format.

You will have to enter the hours, even it it is zero. If you enter 8:24, Excel will take that to be 8 hours 24 minutes.

Reply | Quote