Date format
 

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

Date format
 

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")

--
Regards,
Tom Ogilvy

Gareth wrote in message
...
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

Date format
 

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

Gareth

"Tom Ogilvy" wrote in message
...
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")

--
Regards,
Tom Ogilvy

Gareth wrote in message
...
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

Date format
 

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.

--
Regards,
Tom Ogilvy

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

Gareth

"Tom Ogilvy" wrote in message
...
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")

--
Regards,
Tom Ogilvy

Gareth wrote in message
...
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

Date format
 

On Sun, 18 Jan 2004 20:17:01 -0000, "Gareth"
wrote:

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


How about:

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


--ron

Date format
 

Never heard of/used SendKeys. How would it work in this
situation?

Gareth

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

--
Regards,
Tom Ogilvy

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

Gareth

"Tom Ogilvy" wrote in message
...
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")

--
Regards,
Tom Ogilvy

Gareth wrote in
message
...
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









.