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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Peter - 20 May 2008 23:04 GMT

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.

Reply to this Message

Gord Dibben - 20 May 2008 23:46 GMT

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

>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.

Reply to this Message

Dave Curtis - 21 May 2008 08:28 GMT

Hi, You can use any of the following, formatted appropriately. Some work as
true times, some just display as a time.

=VALUE(LEFT(TEXT(A1,"0000"),2) & ":" & RIGHT(TEXT(A1,"0000"),2))
=TIME((LEFT(A1,LEN(A1)-2)),RIGHT(A1,2),0)
=VALUE(LEFT(TEXT(A1,"0000"),2)&":" &RIGHT(TEXT(A1,"0000"),2))
=IF(LEN(A1)=3,TIME((LEFT(A1,1)),MID(A1,2,2),0),TIME((LEFT(A1,2)),MID(A1,3,2),
0))
=INT(A1/100)/24 + MOD(A1,100)/1440
=LEFT(A1,LEN(A1)-2) & ":" & RIGHT(A1,2)
=TIMEVALUE(TEXT(A1,"#"":""00"))

Dave

url:http://www.ureader.com/msg/104234051.aspx

Reply to this Message