Converting a date to a different date

[Belinda]

I have a database with several hundreds of records with dates that are written incorrectly.
For example, 1/2/1997 is ready by Excel as January 2nd, 1997.
In fact the date is supposed to be February 1st, 1997.
How can I use VBA to convert the date. Thank you.

 

[VoG]

Try this

Sub changeDate()
Dim c As Range
Range("G2:G" & Cells(Rows.Count, "G").End(xlUp).Row).TextToColumns DataType:=xlDelimited, FieldInfo:=Array(1, 2)
For Each c In Range("G2:G" & Cells(Rows.Count, "G").End(xlUp).Row)
    On Error Resume Next
    c.Value = Format(Evaluate("DATE(" & Right(c.Value, 4) & "," & Mid(c.Value, Application.WorksheetFunction.Find("/", c.Value) + 1, Application.WorksheetFunction.Find("/", c.Value, Application.WorksheetFunction.Find("/", c.Value) + 1) - Application.WorksheetFunction.Find("/", c.Value) - 1) & "," & Left(c.Value, Application.WorksheetFunction.Find("/", c.Value) - 1) & ")"), "m/d/yyyy")
    On Error GoTo 0
Next c
Range("G2:G" & Cells(Rows.Count, "G").End(xlUp).Row).TextToColumns DataType:=xlDelimited, FieldInfo:=Array(1, 3)
End Sub

 

[jindon]

I have a database with several hundreds of records with dates that are written incorrectly.
For example, 1/2/1997 is ready by Excel as January 2nd, 1997.
In fact the date is supposed to be February 1st, 1997.
How can I use VBA to convert the date. Thank you.

try

Sub test()
Dim r As Range, x
For Each r In Range("g2", Range("g" & Rows.Count).End(xlUp))
    If r.Value Like "##/##/####" Then
        x = Split(r.Value, "/")
        r.Value = DateSerial(Val(x(2)), Val(x(1)), Val(x(0)))
    End If
Next
End Sub

 

[Belinda]

I still have not been able to resolve the problem. Would appreciate some guidance into how to incorporate the code suggested by schiern:

if not isdate(c) then exit sub

into the original code given on July 7th. Thank you.

 

[schielrn]

Did VoG II's code not work with the on error resume next statement? I feel that should handle what you are looking for?

 

[VoG]

Or perhaps

Sub changeDate()
Dim c As Range
Range("G2:G" & Cells(Rows.Count, "G").End(xlUp).Row).TextToColumns DataType:=xlDelimited, FieldInfo:=Array(1, 2)
For Each c In Range("G2:G" & Cells(Rows.Count, "G").End(xlUp).Row)
    If Not IsError(c.Value) Then
        c.Value = Format(Evaluate("DATE(" & Right(c.Value, 4) & "," & Mid(c.Value, Application.WorksheetFunction.Find("/", c.Value) + 1, Application.WorksheetFunction.Find("/", c.Value, Application.WorksheetFunction.Find("/", c.Value) + 1) - Application.WorksheetFunction.Find("/", c.Value) - 1) & "," & Left(c.Value, Application.WorksheetFunction.Find("/", c.Value) - 1) & ")"), "m/d/yyyy")
    End If
Next c
Range("G2:G" & Cells(Rows.Count, "G").End(xlUp).Row).TextToColumns DataType:=xlDelimited, FieldInfo:=Array(1, 3)
End Sub

 

[jindon]

Or try

Sub test()
Dim r As Range, x
With CreateObject("VBScript.RegExp")
    .Pattern = "^(\d{1,2})/(\d{1,2})/(\d{4})$"
    For Each r In Range("g2", Range("g" & Rows.Count).End(xlUp))
        If .test(r.Value) Then r.Value = .replace(r.Value, "$2/$1/$3")
    Next
End With
End Sub

 

[Belinda]

Thank you all for your feedback. All the codes suggested so far work, as long as there is no cell with "N/A" in the list.
Below is a small sample of the list that I am working with. Can you please try it with your code. Thank you.

Product_Date
1/3/1997
1/9/1992
1/3/2001
1/11/1996
1/4/1992
1/11/1999
N/A
1/11/1997
1/10/1995
1/9/1997
1/3/1999
1/1/1999
N/A
N/A
1/3/2002

 

[VoG]

Please try

Sub changeDate()
Dim c As Range
Range("G2:G" & Cells(Rows.Count, "G").End(xlUp).Row).TextToColumns DataType:=xlDelimited, FieldInfo:=Array(1, 2)
For Each c In Range("G2:G" & Cells(Rows.Count, "G").End(xlUp).Row)
    If Not IsError(c.Value) Then
        If c.Value <> "N/A" Then c.Value = Format(Evaluate("DATE(" & Right(c.Value, 4) & "," & Mid(c.Value, Application.WorksheetFunction.Find("/", c.Value) + 1, Application.WorksheetFunction.Find("/", c.Value, Application.WorksheetFunction.Find("/", c.Value) + 1) - Application.WorksheetFunction.Find("/", c.Value) - 1) & "," & Left(c.Value, Application.WorksheetFunction.Find("/", c.Value) - 1) & ")"), "m/d/yyyy")
    End If
Next c
Range("G2:G" & Cells(Rows.Count, "G").End(xlUp).Row).TextToColumns DataType:=xlDelimited, FieldInfo:=Array(1, 3)
End Sub

 

[Belinda]

Thanks a lot, VOG II. Code works like a Charm!!

 

[jindon]

It will be

Sub test()
Dim r As Range, x
With CreateObject("VBScript.RegExp")
    .Pattern = "^(\d{1,2})/(\d{1,2})/(\d{4})$"
    For Each r In Range("g2", Range("g" & Rows.Count).End(xlUp))
        If (Not IsError(r.Value)) * (.test(r.Value)) Then _
                r.Value = .replace(r.Value, "$2/$1/$3")
    Next
End With
End Sub