You can select column C and then do
Data|Text to columns
Fixed width
and choose dmy
and finish up
(and maybe reformat to the way you want)


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

--

Dave Peterson