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]

Maybe this

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(Mid(.Value, 4, 2) & "/" & Left(.Value, 2) & "/" & Right(.Value, 2))
    End With
Next i
End Sub

 

[Bamerand]

Thanks, I got it working (the previous post). However, could you assist in getting me help in different macro? I have attached a few screen-shots (see screen-shot 01 for clarification).
On daily basis, I work with files that I need to extract data for specific time (04:00, 08:00, 12:00, 16:00, 20:00 and 00:00). "00:00" is for today and the rest of hours go back to yesterday timing (in retrospect). The previous code from web-page "Re: 2-Digit Year" was required to format dates (see screen-shot 02).
In the 3rd screen-shot shown the data I need to pull from each page of a workbook. It is highlighted in green, in the column "I" for the hours 04:00, 08:00, 12:00, 16:00, 20:00 and 00:00 (see screen-shot 03 and screen-shot 05). How can I make a proper macro that loops through entire workbook, copies the required data and pastes (transpose) it in a separate worksheet with name of each worksheet corresponding? See screenshot-4 for clarification (the way I need the data organized).

I tried to do the combination of these macros. But it makes only half of the required task. Is there a way to make it fully automatic? I am using MS Excel 2010.

Here are posted the screenshots.

https://picasaweb.google.com/lh/webUpload?uname=bamerand

Sub Select_Hours()
Selection.AutoFilter Field:=1, Criteria1:=Array("00:00", "04:00", "08:00", "12:00", "16:00", _ 'make selection with mouse
"20:00", "4:00", "8:00", "0:00"), Operator:=xlFilterValues
With Selection
.Interior.Color = 65535
End With
End Sub

Sub GoToDate()
Dim MyRow As Long
With Worksheets("120") 'change name for each worksheet
MyRow = .Columns("A").Find(Date).Row
Application.Goto .Range("A" & MyRow), True
End With
End Sub


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

 

[VoG]

Please start a new thread for this.

 

[Bamerand]

I have already started a new thread for this.