when you get VBA involved, it interprets all dates with a US format bias. If
the string can't be interpreted that way ex: 13/10/2007, then it will use
dd/mm/yyyy so that is why you see mixed results.

--
Regards,
Tom Ogilvy


"Paul J" wrote:

I have inherited worksheets full of business data showing date fields stored
as Text in "dd.MM.yy" (a sort-of UK) date format.

If I manually use the Edit, Replace [Ctrl+H] function on the Worksheet
Column to change "." to "/", and I specify the "dd/MM/yyyy" date format for
output, I can achieve just the results I require.

And if I record a Macro whilst performing this manual exercise (above) it
produces code like this:

Columns("C:C").Select
Application.ReplaceFormat.NumberFormat = "dd/MM/yyyy;@"
Selection.Replace What:=".", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

. . . . . . which looks very promising.

HOWEVER when this VBA code is run it produces some very mixed results!
- some dates (such as 12.06.07 [i.e. 12 Jun 2007] it converts in MM/dd/yyyy
- some dates (such as 27.02.07 [i.e. 07 Feb 2007] it converts to dd/MM/yy
- and the cell is marked to show that it contains a "Text Date with
2-digit Year"

Help!

Can anybody advise me, please?

Paul J