Format cells for date or time without having to enter the / or :

[Darcyyc]

I am trying to find out how to enter date and enter time without entering the
/ and the : each time; i.e. 12312007 and have it appear as 12/31/2007 or 831
and have it appear as 8:31. I used to know how to do this but have
forgotten...duh!

 

[Gord Dibben]

Cannot be done without VBA

See Chip Pearson's site for quickentry code.

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


Gord Dibben MS Excel MVP

 

[Dave Curtis]

Hi,,
If you're happy to have the result you want appear in the adjacent cell,
rather than the entry cell, can easily be done with a formula, otherwise VBA
as Gord says.

If A1 contains 16102008 then in B1,
=DATE(LEFT(A1,2),MID(A1,3,2),MID(A1,5,4))
will give you 16/10/2008

Or for times, if A1 contains 1035 then in B1 any of the following will give
you 10:35
=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"))
and if you want 10:35AM try
=TIME((LEFT(A1,LEN(A1)-2)),RIGHT(A1,2),0)

Dave

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