Link to home
Start Free TrialLog in
Avatar of Jagwarman
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(xlLastCell).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
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
try this
Sub Convert_Dates()
    Dim rng As Range
    Dim cl As Object
    Set rng = Range("F2:F" & Cells.SpecialCells(xlLastCell).Row)
    For Each cl In rng
        If cl.Value <> "" Then
            With cl
            .NumberFormat = "@"
            dim sDay as string
            sDay = day(cl.value)
            if len(sDay) = 1 then
                day = "0" & day
            endif    
            cl.Value = sDay & _
                Format(cl.Value, "mmm") & _
                Format(cl.Value, "yy")
            End With
        End If
    Next cl
End Sub

Open in new window

Avatar of Jagwarman
Jagwarman

ASKER

Rgonzo1971 I get Syntax error on both
Anthony Berenguel

I get Argument not optional at

Day = "0" & Day
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
On which line no error for me
Thanks have a great weekend