[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

vba that will change text date from 8AUG2014 to 08AUG2014

Posted on 2014-08-08
7
Medium Priority
?
181 Views
Last Modified: 2014-08-08
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
0
Comment
Question by:Jagwarman
  • 3
  • 2
  • 2
7 Comments
 
LVL 54

Accepted Solution

by:
Rgonzo1971 earned 1000 total points
ID: 40248721
HI,

pls try

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 = Format(cl.Value, "dd") & _
                Format(cl.Value, "mmm") & _
                Format(cl.Value, "yy")
            End With
        End If
    Next cl
End Sub

'Or


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 =   Format(cl.Value, "ddmmmyy")
            End With
        End If
    Next cl
End Sub

Regards
0
 
LVL 10

Expert Comment

by:Anthony Berenguel
ID: 40248728
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

0
 

Author Comment

by:Jagwarman
ID: 40248771
Rgonzo1971 I get Syntax error on both
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Jagwarman
ID: 40248779
Anthony Berenguel

I get Argument not optional at

Day = "0" & Day
0
 
LVL 10

Assisted Solution

by:Anthony Berenguel
Anthony Berenguel earned 1000 total points
ID: 40248804
sorry!
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
                sday = "0" & sday
            endif    
            cl.Value = sDay & _
                Format(cl.Value, "mmm") & _
                Format(cl.Value, "yy")
            End With
        End If
    Next cl
End Sub

Open in new window

0
 
LVL 54

Expert Comment

by:Rgonzo1971
ID: 40248810
On which line no error for me
0
 

Author Closing Comment

by:Jagwarman
ID: 40248901
Thanks have a great weekend
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question