Excel VBA to convert "dd.MM.yy" text to "dd/MM/yyyy" format date?

[Paul J]

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

 

[Tom Ogilvy]

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.

 

[Dave Peterson]

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)

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

 

[ward376]

You could also use a text formula to get the actual date...

=VALUE(CONCATENATE(MID(A2,4,2),"/",LEFT(A2,2),"/",RIGHT(A2,2)))

and manipulate in vba to get what you want.

 

[Paul J]

Seeing as VBA doesnt handle non-US date formats in this instance, and taking
up ward376's suggestion, I will use the following:

Dim RecordCounter As Long ' Number of Records to be Processed
RecordCounter = 15 ' Including Header
' Reformat dd.MM.yy dates in column F into dd/MM/yyyy Excel dates in column
C via column E
Range("F1").Copy Destination:=Range("E1") ' Copy Header
Dim i As Long
For i = 2 To RecordCounter ' Fill in with Excel formulae
Range("E" & i).FormulaR1C1 = _

"=DATEVALUE(CONCATENATE(LEFT(RC[1],FIND(""."",RC[1])-1),""/"",MID(RC[1],(FIND(""."",RC[1])+1),((FIND(""."",RC[1],(FIND(""."",RC[1])+1))-(FIND(""."",RC[1])+1)))),""/"",RIGHT(RC[1],LEN(RC[1])-FIND(""."",RC[1],FIND(""."",RC[1])+1))))"
Next i
Columns("E:E").NumberFormat = "dd/mm/yyyy;@" ' Format the column
Columns("E:E").Copy ' Copy Values (lose the formulae) and Formats
Columns("C:C").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Columns("C:C").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False

It's a bit "intense" but it does the job.

Thanks for all your help.

Paul J