Cell Formatting Issue

Copper Contributor

This seems ridiculous. Simple employee timesheet. Most cells format OK. Simple sum and other calculations. When trying to format cells for in/out times (e.g.; 9:00 AM), sheet always reverts to date and time format that does not correspond to data input (e.g.; 6/18/1902 12:00 AM). Can anyone help?

4 Replies


This means that the user entered 900 instead of 9:00


Excel stores both dates and times internally as numbers. 900 corresponds to 6/18/1902.

OK. That works. Thanks. Seems one should be able to simply input a '9' in the cell and the formatting would alter it to '9:00 AM' if that was the format selected.


Excel simply doesn't work that way.

See Date And Time Entry for a way to make Excel convert 900 (but not 9) to 9:00 automatically in certain cells. It requires VBA.

OK. Thank you for taking the time to provide assistance. I'm one of those who knows just enough to be dangerous.