Date format

G

Gareth

I copy data from a Word document into Excel. I then run a macro to arrange
this data in the correct format for users.

One of the column's contains dates, but instead of the / separator they are
separated by a .

Eg.
31.12.02
02.10.02

I use the following to replace them:

Range("F2:F" & Range("F65536").End(XlUp).Row).Replace ".","/"

And get:
31/12/02
10/02/2002

Is it something to do with the US date format or am I missing something
really easy?

Thanks in advance.

Gareth

NB
My PC date settings are dd/mm/yyyy
 
T

Tom Ogilvy

Yes. Generally if a data can be interpreted as a valid date using a US
format interpretation, it will be interpreted that way.

cdate is supposed to observe your regional settings and it appears to accept
period as a separator.

try
cdate("31.12.02")
cdate("02.10.02")
 
G

Gareth

thanks Tom, problem is the column has several 100 dates, is it possible to
CDate them in one go?

Gareth
 
T

Tom Ogilvy

No.

You would have to loop through them.

If you do the replace manually, you shouldn't have the problem. If you need
a macro, try using sendkeys to execute the replacement and you probably
won't have the problem.
 
R

Ron Rosenfeld

I copy data from a Word document into Excel. I then run a macro to arrange
this data in the correct format for users.

One of the column's contains dates, but instead of the / separator they are
separated by a .

Eg.
31.12.02
02.10.02

I use the following to replace them:

Range("F2:F" & Range("F65536").End(XlUp).Row).Replace ".","/"

And get:
31/12/02
10/02/2002

Is it something to do with the US date format or am I missing something
really easy?

Thanks in advance.

Gareth

NB
Click to expand...


How about:

=====================
Range("F2:F" & Range("F65536").End(XlUp).Row).TextToColumns _
Destination:=Range("F2"), _
DataType:=xlFixedWidth, _
FieldInfo:=Array(0, xlDMYFormat)
=====================


--ron
 
Top