macro to convert 19xx to 20xx?

[daveyc18]

when i record, it doesn't record the action

it's in column i

 

[Peter_SSs]

Try with a copy of your data

Sub FixDates()
  Columns("A").TextToColumns DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 4))
End Sub

 

[daveyc18]

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

but some dates are already in 20xx...., would it add 100 to that? i believe it's years after 2029 that excel makes it based on the 20th century (eg 30 = 1930)

i heard you can change this default behaviour by changing your windows settings, but this isn't a personal file and not everyone at work will change the settings

 

[Peter_SSs]

.. but some dates are already in 20xx...

From the sample file that I downloaded all values in column A are TEXT values and all only have a 2 year date figure. So from your question I assumed that all existing dates were 19xx dates and you wanted them converted.

Could you indicate which ones are already 20xx and how we can tell that from what is in the cells?

 

[daveyc18]

From the sample file that I downloaded all values in column A are TEXT values and all only have a 2 year date figure. So from your question I assumed that all existing dates were 19xx dates and you wanted them converted.

Could you indicate which ones are already 20xx and how we can tell that from what is in the cells?

I want everything to be in 20xx

 

[Peter_SSs]

I want everything to be in 20xx

Unfortunately, that does not answer either of my two questions.

Could you indicate which ones are already 20xx and how we can tell that from what is in the cells?

 

[Alex Blakenburg]

the date in original form is mm/dd/yy

On the one hand you say the date is in the format mm/dd/yy but you then provided a file with all the dates being Text (not dates) and in the format dd-mmm-yy.
What is your process for getting the data ? What is the earliest step in which the macro could be run and can you give us a file with the dates at that point ?

Based on your sample data in Post #8 either of these should work.
The 2nd will work even if some some of the text values have been converted to dates.

Option 1: Text values per sample file dd-mmm-yy

Sub TextDateMake2000()
    With Range("A2", Cells(Rows.Count, "A").End(xlUp))
        .Value = Application.Replace(.Value, 8, 0, "20")
    End With
End Sub

Option 2: Values per sample file either text or date

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

 

[daveyc18]

Unfortunately, that does not answer either of my two questions.

nothing should be in 19xx

 

[daveyc18]

On the one hand you say the date is in the format mm/dd/yy but you then provided a file with all the dates being Text (not dates) and in the format dd-mmm-yy.
What is your process for getting the data ? What is the earliest step in which the macro could be run and can you give us a file with the dates at that point ?

Based on your sample data in Post #8 either of these should work.
The 2nd will work even if some some of the text values have been converted to dates.

Option 1: Text values per sample file dd-mmm-yy

Sub TextDateMake2000()
    With Range("A2", Cells(Rows.Count, "A").End(xlUp))
        .Value = Application.Replace(.Value, 8, 0, "20")
    End With
End Sub

Option 2: Values per sample file either text or date

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

based on the sample provided, pls make sure nothing shows up as 19xx

 

[Alex Blakenburg]

Did you try both macros ? What happened ? If it didn't work give an example of a couple that didn't work.

 

[daveyc18]

let me put this way

manually excel gives me the option...19xx or 20xx...i range 20xx