Jagwarman
asked on
vba that will change text date from 8AUG2014 to 08AUG2014
Hi is there an Expert out there that can help me to change dates from 8AUG2014 to 08AUG2014
Glenn Ray provided me with below code to change excel date format to Text but when it does this it puts single numeric dates to single numeric Text so 08/08/2014 changes to 8AUG2014 but I need it to be 08AUG2014
Sub Convert_Dates()
Dim rng As Range
Dim cl As Object
Set rng = Range("F2:F" & Cells.SpecialCells(xlLastC ell).Row)
For Each cl In rng
If cl.Value <> "" Then
With cl
.NumberFormat = "@"
cl.Value = Day(cl.Value) & _
Format(cl.Value, "mmm") & _
Format(cl.Value, "yy")
End With
End If
Next cl
End Sub
Thank you in advance
Glenn Ray provided me with below code to change excel date format to Text but when it does this it puts single numeric dates to single numeric Text so 08/08/2014 changes to 8AUG2014 but I need it to be 08AUG2014
Sub Convert_Dates()
Dim rng As Range
Dim cl As Object
Set rng = Range("F2:F" & Cells.SpecialCells(xlLastC
For Each cl In rng
If cl.Value <> "" Then
With cl
.NumberFormat = "@"
cl.Value = Day(cl.Value) & _
Format(cl.Value, "mmm") & _
Format(cl.Value, "yy")
End With
End If
Next cl
End Sub
Thank you in advance
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Rgonzo1971 I get Syntax error on both
ASKER
Anthony Berenguel
I get Argument not optional at
Day = "0" & Day
I get Argument not optional at
Day = "0" & Day
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
On which line no error for me
ASKER
Thanks have a great weekend
Open in new window