macro to convert 19xx to 20xx?

[daveyc18]

when i record, it doesn't record the action

it's in column i

 

[Alex Blakenburg]

Thanks for the confirmation. Glad we could help.

 

[daveyc18]

Hello,
Given the provided informations, the sub below should give the expected results.

Sub Replace19xxTo20xx()
  Dim myVals As Variant, i As Long
  With ActiveSheet.Range("I1")
    myVals = Range(.Cells, .End(xlDown)).Value2
  End With
  myVals = WorksheetFunction.Transpose(myVals)
  For i = LBound(myVals) To UBound(myVals)
    If myVals(i) Like "19##" Then myVals(i) = CLng("20" & Right$(myVals(i), 2))
  Next i
 
  ActiveSheet.Range("I1").Resize(UBound(myVals), 1).Value2 = WorksheetFunction.Transpose(myVals)
End Sub

Actually, how would you make it work

It is extremely important to provide a representative sample of data. I have modified the code below and is should handle the 2 scenarios you have described.
However it still is based around the sample file you provided with a date format of dd-mmm-yy.
You reference to the file containing 00/00/0000 indicates that this may not be the case and you have not responded to repeated questions on showing use the original data format.

Make sure you check a sample of dates in particular one with the day value being <= 12 and one with the day value being >12.
If your initial date format is in fact mm/dd/yy it is likely the code below will reverse the day and month.

Sub DateMake2000()

    Dim rng As Range
    Dim arr As Variant
    Dim dtTemp As Date
    Dim i As Long
   
    Set rng = Range("I2", Cells(Rows.Count, "").End(xlUp))
    arr = rng.Value
   
    For i = 1 To UBound(arr)
        If IsDate(arr(i, 1)) Then
            dtTemp = CDate(arr(i, 1))
            If Year(dtTemp) < 2000 Then dtTemp = DateSerial(Year(dtTemp) + 100, Month(dtTemp), Day(dtTemp))
           
            arr(i, 1) = dtTemp
        End If
    Next i
   
    rng.Value = arr

End Sub

actually, how would you make it work if the date column is now in this format

31-Mar-40

 

[Alex Blakenburg]

My code in Post 29 seems to have an error in it and was missing the I in the line:

    Set rng = Range("I2", Cells(Rows.Count, "I").End(xlUp))

That code should have worked fine on 31-Mar-40.
If its not give us some examples showing the original data, the expected outcome and what the macro is producing.