Enter number in a cell and it is to display time.

[Peter]

I have tried everything to format the cell. What I want to do is enter 1005
and in the cell it reads 10:05. I have tried [h]:mm, 13:30, and many other
combinations. It either displays 0:00 or 00:00 or 10/1/1902 12:00:00 AM.

 

[Gord Dibben]

You cannot do this with formatting alone.

You will need a formula in an adjacent cell to return the time.

Or VBA event code to make the change to 10:05 as you enter it.

Formula.........=TIMEVALUE(TEXT(A1,"#"":""00")) formatted as h:mm:ss am/pm

Event code......................

See Chip Pearson's site for code.

http://www.cpearson.com/excel/DateTimeEntry.htm


Gord Dibben MS Excel MVP