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.

 

[Michael M]

Hi Belinda
Have you tried simply changing the custom format of the cells to date: dd/mm/yyyy

Regards
Michael M

 

[schielrn]

THe code on this link may work for you:

http://www.ozgrid.com/forum/showthread.php?t=63888

Otherwise you may have to convert the cells to text and then use the text dates with mid, left and right functions to fill in the correct non-US date. I assume you don't need to just change the formatting.

If you have any problems please post back and I can try to come up with some code to convert dates.

 

[Michael M]

Hi Schierln
I had a look at the link and I see the point you are making.
It's a handy explanation too.

As my tag says.........

Regards
Michael M

 

[schielrn]

Ok I was interested in this, so maybe 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)
    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")
Next c
Range("G2:G" & Cells(Rows.Count, "G").End(xlUp).Row).TextToColumns DataType:=xlDelimited, FieldInfo:=Array(1, 3)
End Sub

Change the column range to your needs. Hope that helps.

 

[Belinda]

Thanks very much, Schielrn . Code worked perfectly.

 

[Belinda]

I have come across a small problem. Some of the records have "N/A" instead of dates.
How do I adjust the code, so that it skips the cells that don't have dates. Thanks.

 

[schielrn]

Maybe at the very beginning of the for loop put:

if not isdate(c) then exit sub

Or something to that effect. Hope that helps.

 

[VoG]

I have come across a small problem. Some of the records have "N/A" instead of dates.
How do I adjust the code, so that it skips the cells that don't have dates. Thanks.

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

 

[Belinda]

The code still stop when it comes across a cell that has "N/A" in it.
How about adjusting the code so that it will skip any cell that has "N/A"?