macro to convert 19xx to 20xx?

[daveyc18]

when i record, it doesn't record the action

it's in column i

 

[daveyc18]

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

ilk try shortly. appreciate the effort

 

[MARK858]

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

You only see that option because your cells are Text and not "real dates" as Excel sees them

 

[daveyc18]

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

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

didnt work unfortuantely

 

[daveyc18]

You only see that option because your cells are Text and not "real dates" as Excel sees them

got it, so is there an automated solution? macros provided by other users didnt work, unfortunately

 

[MARK858]

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.

@daveyc18 which of the above is it (as it appears to you in the cell)? and is the column still column I and the headers in I1?

 

[daveyc18]

@daveyc18 which of the above is it (as it appears to you in the cell)? and is the column still column I and the headers in I1?

yes .. header in column i1

 

[Alex Blakenburg]

Did you change the Set rng line in both my macros from A to I ?

 

[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

thanks, the second code seems to work

couple of issues...any dates that are "00/00/000" causes it to say "mismatch"......cant fix it? fine, I can clear these dates beforehand then run your macro

the other issue...any fields that are blank are filled with "12/30/1999"

 

[Alex Blakenburg]

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

 

[daveyc18]

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

wroks! thanks!