Formatting date values in VBA for various iterations of a date

In Excel VBA I am trying to determine how I can consistently format the below value into dd/mm/yyyy if I receive the cell value provides the below values ?  I tried format.range(***,"dd/mm/yyyy") but the converted values  are inconsistent depending on how someone sends me the values (see below possible received values).


All the above I need to to convert to 01/02/2017
Who is Participating?
Fabrice LambertConnect With a Mentor Fabrice LambertCommented:

Maybe with the code below, then just input your data in your cells:
Dim wb As Excel.Workbook
Set wb = ThisWorkbook

Dim ws As Excel.Worksheet
Set ws = wb.Worksheets(1)

Dim rng As Excel.Range
Set rng = ws.Range("A1:A10")
rng.NumberFormat = "dd/mm/yyyy"

Open in new window

upobDaPlayaAuthor Commented:
An edit to the question - Once formatted though I need to store as text and can not drop any leading zeros.  Sorry for the omission.
upobDaPlayaAuthor Commented:
Actually this helped...from

Sub SingleQuote()
Dim r As Range, s As String
For Each r In Selection
    If Not r.HasFormula Then
        s = r.Text                     ' gets text displayed in cell
        r.NumberFormat = "@"     ' sets cell format as text
        r.Value = s                    ' writes the new value in the cell
    End If
Next r
End Sub
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.