vba that will change excel date to text date

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

Flyster's code preserves the internal date (41842); mine converts it to text.

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

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

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

Select allOpen 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

Thanks Glenn

You're welcome.

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?