Peculiar date format behavior when replacing parts of date
 

I have a file in which one column consists of dates in a "dd.mm.yyyy" format
(e.g. 25.12.2006). I need to replace the "." with a "/" to obtain a
"dd/mm/yyyy" format (i.e. 25/12/2006).

When I do that manually with search and replace it works fine. However when
I record a macro to do the job the following happens: all dates there the dd
is 13 and above convertes correctly (e.g. 15.11.2006 becomes 15/11/2006);
however all dates where the dd is 12 or less swaps the dd and mm, e.g.
12.11.2006 becomes 11/12/2006! It looks as if the macro causes some of the
records to assume a US-type mm/dd/yyyy format.

I have tried to define the date format in different ways both before and
during the replacement but nothing seems to work.

I'm using Excel 2003 (11.8120.8122) SP2, fully opdated.

Anybody has an idea about how to solve this?

Thanks
Henrik

Peculiar date format behavior when replacing parts of date
 

Record a macro when you
select that column
Data|Text to columns
Fixed width (but remove any line that excel guessed)
and choose date (dmy) as the format

Henrik wrote:

I have a file in which one column consists of dates in a "dd.mm.yyyy" format
(e.g. 25.12.2006). I need to replace the "." with a "/" to obtain a
"dd/mm/yyyy" format (i.e. 25/12/2006).

When I do that manually with search and replace it works fine. However when
I record a macro to do the job the following happens: all dates there the dd
is 13 and above convertes correctly (e.g. 15.11.2006 becomes 15/11/2006);
however all dates where the dd is 12 or less swaps the dd and mm, e.g.
12.11.2006 becomes 11/12/2006! It looks as if the macro causes some of the
records to assume a US-type mm/dd/yyyy format.

I have tried to define the date format in different ways both before and
during the replacement but nothing seems to work.

I'm using Excel 2003 (11.8120.8122) SP2, fully opdated.

Anybody has an idea about how to solve this?

Thanks
Henrik

--

Dave Peterson