Solved

vba that will change excel date to text date

Posted on 2014-07-22
9
1,907 Views
Last Modified: 2014-08-08
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
Comment
Question by:Jagwarman
  • 4
  • 4
9 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 40213012
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
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40213016
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40213019
Flyster's code preserves the internal date (41842); mine converts it to text.
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:Jagwarman
ID: 40213511
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
 

Author Comment

by:Jagwarman
ID: 40213560
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
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40214651
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
 

Author Comment

by:Jagwarman
ID: 40215478
Thanks Glenn
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40215531
You're welcome.
0
 

Author Comment

by:Jagwarman
ID: 40248434
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

840 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