• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2300
  • Last Modified:

vba that will change excel date to text date

is it possible to change the cell format in excel from Date to text so that 22/07/2014 will become 27Jul14 instead of 41842 using VBA code.

Thanks
0
Jagwarman
Asked:
Jagwarman
  • 4
  • 4
1 Solution
 
FlysterCommented:
This will give you the formatting you want. Just change the range to what you need:

Sub FormatCell()
    Range("A1").Select
    Selection.NumberFormat = "ddmmmyy"
End Sub

Flyster
0
 
Glenn RayExcel VBA DeveloperCommented:
This code converts the format for the active (selected cell):
Sub Convert_Date()
    With ActiveCell
        .NumberFormat = "@"
        ActiveCell.Value = Day(ActiveCell.Value) & _
            Format(ActiveCell.Value, "mmm") & _
            Format(ActiveCell.Value, "yy")
    End With
End Sub

Open in new window


-Glenn
0
 
Glenn RayExcel VBA DeveloperCommented:
Flyster's code preserves the internal date (41842); mine converts it to text.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
JagwarmanAuthor Commented:
Glenn is correct Flyster, I needed to change it to text but thanks for trying.

Glenn that is exactly what I am looking for many thanks
0
 
JagwarmanAuthor Commented:
GlennRay is it possible for this to work over a range i.e. F:F or F:G I presumed I could change ActiveCell to ActiveRange but nothing is ever simple.

Thanks
0
 
Glenn RayExcel VBA DeveloperCommented:
Sorry for the late reply, but yes, here's modified code to run over all values in a single column (in this example, column F):
 
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

Open in new window


You could change the column in line 4 in the Range("F2:F" portion, even expand it to column G as you suggested by changing the second "F" in that part.

-Glenn
0
 
JagwarmanAuthor Commented:
Thanks Glenn
0
 
Glenn RayExcel VBA DeveloperCommented:
You're welcome.
0
 
JagwarmanAuthor Commented:
Glenn, hope you will pick this up. Your code works great but i need to have the date format like 01AUG2014,  08AUG2014 and not 1AUG2014, 8AUG2014

Can you help?
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now