# Excel 2002 cell format for Custom/mm:ss returns date



## rdoty (Feb 24, 2005)

I have a relatively simple calculation spreadsheet with a frustrating problem. When I format a cell as "Custom/mm:ss" and then enter either 45 or 45:00 the result shows only "00:00" and the Formula Bar shows either "2/14/1900 12:00:00 AM" or "1/1/1900 9:00:00 PM" depending on the entry. Also, another cell which includes this cell in a sum returns only "0:00". How do I correct this so that I can enter time as mm:ss and then total several cells as mm:ss?


----------



## kljordan (Oct 28, 2004)

*Try the Time format*

I use a simular type of measurement and have the cells formated to Time 13:30. I believe this will give you what you want.


----------



## rdoty (Feb 24, 2005)

I tried that initially and it also didn't work properly e.g. if I format cell as Time 13:30 and then enter "12:25" for twelve minutes and twenty five seconds the Formula bar shows it as "12:25:00 PM" which is why I went to the Custom format which has a mm:ss.


----------



## TwistedBard (Feb 25, 2005)

The thing to keep in mind about time formats in Excel is that whatever time you put in there, Excel is not seeing as a time in minutes and seconds. It is seeing a time of day. Always. Okay, there's actually more to it than that. It's really seeing it as a number that represents a specific time AND date, but that's another whole story. Just ignore that for now. LOL. 

In essence, the formats for time are just different ways of representing a time of day. This has the effect of making the entry of time somewhat tricky. The easiest way I can think of to tell you how to do what it sounds like you're trying to do, is to remember that Excel, regardless of how you format the cell, is looking for time to be entered as h:mm:ss. 

If you format your cell as mm:ss, and enter 45:00, it sees 45 hours and 00 minutes, which can't be represented in your format, so you get 00:00. If you want to show 45 minutes, enter 00:45 this will give you 45:00 (45 minutes, 0 seconds) in your cell after you press enter. If you want to enter 45 seconds, type 0:0:45. 

Hope that helps!


----------

