macro to convert 19xx to 20xx?

[daveyc18]

when i record, it doesn't record the action

it's in column i

 

[saboh12617]

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

 

[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

doesn't seem to work ..

Loading Google Sheets

 

[saboh12617]

Hello,

Well it works fine on Excel, if you are using google sheets that's a different type of macro you need to use, called AppScript. Also you're not on the right forum section if i'm not mistaken.

 

[daveyc18]

Hello,

Well it works fine on Excel, if you are using google sheets that's a different type of macro you need to use, called AppScript. Also you're not on the right forum section if i'm not mistaken.

i tried in EXCel, but ill try again..ty

 

[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

yah it doesnt work..i think the reason is that....the date in original form is mm/dd/yy

thats why excel asks if you want 19 or 20 as the year

ill upload sample data shortly

 

[MARK858]

yah it doesnt work..i think the reason is that....the date in original form is mm/dd/yy

Can you enter the formula

=ISNUMBER(A2)

in a blank cell and change the A2 to one of the cells containing the date and let us know whether you get TRUE or FALSE please?

Also can you post how your dates are displayed please? and what you see in the formula bar when you click one of the cells with a date in it?

 

[daveyc18]

Can you enter the formula

=ISNUMBER(A2

) in a blank cell and change the A2 to one of the cells containing the date and let us know whether you get TRUE or FALSE please?

Also can you post how your dates are displayed please? and what you see in the formula bar when you click one of the cells with a date in it?

Loading Google Sheets

 

[MARK858]

Please enter the formula as requested and post the result and answer the formula bar question

 

[Skyybot]

Book1
	A	B
1	1/1/1925	1/1/2025
Sheet1

Cell Formulas
Range	Formula
B1	B1	=Add100Years(A1)

Option Explicit
Public Function Add100Years(ByRef rng As Range) As Date
Dim sDate As Date
sDate = rng.Value
Add100Years = DateAdd("yyyy", 100, sDate)
End Function