2-Digit Year

[dalameda]

after copying data into excel, it brings over a column of dates showing as "01-Feb-10" for example. This date is stored as a 2 year date. I have formulas that are comparing the dates in this coumn with the current date, but of course it doesnt work right when comparing a 2-digit year with a 4-digit year.
does anybody know how i can create a macro that would start in a certain cell (say G16) automatically go down to the last data string showing in that column (which could change) and "convert XX to 20XX."??
<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"> <v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype>

 

[VoG]

Isn't it just a case of changing the cell formatting or are these text dates. If they are text dates, select them, Data > Text to Columns, click Next twice, tick Date and select DMY then click Finish.

 

[dalameda]

i can easily select the date range manually and click the "error icon" that appears and choose to change it to a 20XX date, but i am creating this for novice users and would like to just attach a macro to a button that would do that for them.

 

[VoG]

Are they text or are they dates?

In a spare cell enter

=ISNUMBER(G16)

What does it return?

 

[dalameda]

and no it is not a date stored as text, it is a date but in the format it is in, Excel doesnt know if it is "1910" or "2010"

 

[dalameda]

it retuns FALSE

 

[VoG]

So they are text dates

Try

Sub ChDates()
Dim LR As Long, i As Long
LR = Range("G" & Rows.Count).End(xlUp).Row
For i = 16 To LR
    With Range("G" & i)
        .NumberFormat = "dd/mm/yyyy"
        .Value = DateValue(.Value)
    End With
Next i
End Sub

 

[dalameda]

ooops, yes they are text...sorry

the code worked...awesome stuff. THANK you VERY much!!!!!!!!!!!!

 

[dalameda]

ok...i tried to paste new data in a different column to test the macro again and I get a "type mismatch" debug error and the line highlighted is ".Value = DateValue(.Value)"
the differences in the columns is in column G (like the original code uses" the data format is "01-Feb-10" (the macro still works for that column). in column N, it the exact same issue except the format of the data that is pasted is "01/29/10" I did test it and it it is stored as text.
i did change both "G"s to be "N".

any suggestions?

 

[Bamerand]

Neither could I work it out too. The same issue with "type mismatch" debug error and the line highlighted is ".Value = DateValue(.Value)". Can anybody tell what could be the cause of the problem?