Dates converting to American format when using CDATE?

[Trebor8484]

Hi all,

I have an issue with the code below where I am attempting to amend incorrectly formatted dates so any instance of "." should change to "/".

The first piece of code seems to convert everything incorrectly:

Sub DateConv()


    Dim LrDst As Long
    Dim DstSht As Worksheet
    Dim StrDate As String
    Dim c As Range
    
    Set DstSht = ActiveSheet
    LrDst = DstSht.Cells(Rows.Count, "A").End(xlUp).Row


    For Each c In DstSht.Range("A1:A" & LrDst)
        If IsDate(c.Value) And c.Value <> "" Then
            StrDate = Left(c, 2) & "/" & Mid(c, 4, 2) & "/" & Mid(c, 7, 100)
            c.Value = Format(CDate(StrDate), "dd/mm/yyyy")
        End If
    Next c
    
End Sub

[/IMG]



The second piece of code works slightly better but still has errors.

Sub DateConv()


    Dim LrDst As Long
    Dim DstSht As Worksheet
    Dim StrDate As String
    Dim c As Range
    
    Set DstSht = ActiveSheet
    LrDst = DstSht.Cells(Rows.Count, "A").End(xlUp).Row


    For Each c In DstSht.Range("A1:A" & LrDst)
        If IsDate(c.Value) And c.Value <> "" Then
            StrDate = Left(c, 2) & "/" & Mid(c, 4, 2) & "/" & Mid(c, 7, 100)
            c = StrDate
            c.Value = Format(CDate(c), "dd/mm/yyyy")
        End If
    Next c
    
End Sub

[/IMG]

Any suggestions please or a better way of dealing with this issue?

Thanks

 

[Fluff]

Try

Sub DateConv()
    Dim LrDst As Long
    Dim DstSht As Worksheet
    
    Set DstSht = ActiveSheet
    LrDst = DstSht.Cells(Rows.Count, "A").End(xlUp).Row

    DstSht.Range("A1:A" & LrDst).Replace ".", "/", xlPart, , , , False, False
End Sub

 

[Trebor8484]

Works great thanks

 

[Fluff]

You're welcome