I would like to change . to / of date fields in column M. If I try to find
and replace individually all works Ok but if I use replace all it seem to
change the date. For example when I use replace all for 08.03.2008 it ends up
as 03/08/2008 but 18.03.2008 changes OK to 18/03/2008. Anyone know what I can
do to stop day and month being switched when replace all is used?

Thank you
Prema

try setting the column's format to TEXT before you run the change

"Prema" wrote:

I would like to change . to / of date fields in column M. If I try to find
and replace individually all works Ok but if I use replace all it seem to
change the date. For example when I use replace all for 08.03.2008 it ends up
as 03/08/2008 but 18.03.2008 changes OK to 18/03/2008. Anyone know what I can
do to stop day and month being switched when replace all is used?

Thank you
Prema

this code should always work

newdate = DateSerial(Mid(olddate, 7, 4), Mid(olddate, 4, 2), Left(olddate, 2))

"Patrick Molloy" wrote:

try setting the column's format to TEXT before you run the change

"Prema" wrote:

I would like to change . to / of date fields in column M. If I try to find
and replace individually all works Ok but if I use replace all it seem to
change the date. For example when I use replace all for 08.03.2008 it ends up
as 03/08/2008 but 18.03.2008 changes OK to 18/03/2008. Anyone know what I can
do to stop day and month being switched when replace all is used?

Thank you
Prema

It'll depend on what your windows regional settings for the short date is.

If you have mdy as your short date, then 08.03.2008 will be changed to August 3,
2008.

If you have dmy as your short date, then 08.03.2008 will be changed to March 8,
2008.

So you may find that some of the values actually converted to a date--but not
the date that you wanted.

If your dates are in a single column, you can use:
Select the column
data|text to columns
choose delimited by nothing
and make sure you choose the correct order for the date to match the data.

If you have multiple columns of dates, you can do it multiple times.

Alternatively, you could change your windows regional settings (under control
panel) short date to the order that you need. Then do the edit|replace. And
change that setting back.

I find that formatting my dates in an unambiguous format, like: mmmm dd, yyyy
makes it much easier to check my work.

Prema wrote:

I would like to change . to / of date fields in column M. If I try to find
and replace individually all works Ok but if I use replace all it seem to
change the date. For example when I use replace all for 08.03.2008 it ends up
as 03/08/2008 but 18.03.2008 changes OK to 18/03/2008. Anyone know what I can
do to stop day and month being switched when replace all is used?

Thank you
Prema

--

Dave Peterson