Date format

  • Thread starter Thread starter Gareth
  • Start date Start date
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
 
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")
 
thanks Tom, problem is the column has several 100 dates, is it possible to
CDate them in one go?

Gareth
 
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.
 
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
 
Back
Top