Date Format

[VbaHell]

Hello All, I am using Excel 2007 and require Help Please.
I import a report into a Tab called "MIS - Customer Invoice" which populates column "A:AO" First Row being the Header.
Column "E" is a date which is a Text Date with 2 digit year (Excel shows the same Triangle error)
The problem I have is Excel is not consistant with this date. When I try to format this some of the dates come out wrong IE: 12/1/2012 0r 1/12/2012
I have tried various way to solve this but I really just guessing.
I have also added another column "AP" to reformat the date just to show "mmm yy" which is used for loads of calcs.

Any ideas please on how to solve this first date issue

Sub UpdateData()
Dim LR As Long
Dim LC As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
LC = Cells(3, Columns.Count).End(xlToLeft).Column
Sheets("MIS - Customer Invoice").Select
Columns("E:E").Select
Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 4), TrailingMinusNumbers:=True


With Range("AP2:AP" & LR)
.FormulaR1C1 = "=TEXT(RC[-37],""mmm yy"")"
'.Value = .Value

End With

 

[drsarao]

Try this:

.FormulaR1C1 = "=TRIM(TEXT(RC[-37]),""mmm yy"")"