Enter date and time in cell

T

tankerman

Is there any way of entering the date and time without having to put the
separtors in (01/01/2007 21:15), how can I just enter the numbers
010120072115 and have EXCEL put the separtors in for me, can I get my cells
to look like this somehow or am I just hoping for too much _ _ / _ _ / _ _
_ _ / _ _ : _ _
 
G

Gord Dibben

Excel has no "input mask" type of function/format that will enter dates in this
manner.

Using event code it is possible.

See Chip Pearson's site for details and code.

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

Otherwise you would need a helper cell with a formula to parse the date and
time.


Gord Dibben MS Excel MVP
 
P

paul

ctrl and semi colon(;) wil give you the date
ctrl and shift then press semi colon,will give you the time.insert the date
and time in one cell with no space or a space or any other character in
between

-
paul
[email protected]
remove nospam for email addy!
 
T

tankerman

At CPEARSON.COM I was able to get the format I am looking for but the formats
are for individual cells and I just can not seem to figure out how to get
them in the same cell because I am having to subtract the "TIME and DATE" of
one cell from the "TIME and DATE" of another. I can get it to work perfectly
if I put in the separators and space myself.
 
D

David Biddulph

You could try
=DATE(MID(A1,LEN(A1)-7,4),MID(A1,LEN(A1)-9,2),LEFT(A1,LEN(A1)-10))+TIME(MID(A1,LEN(A1)-3,2),MID(A1,LEN(A1)-1,2),0)
and format as dd/mm/yyyy hh:mm
 
Top