upobDaPlaya
asked on
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/yy
2/1/2017
2/1/17
2/01/17
02/01/2017
02/1/17
02/1/2017
All the above I need to to convert to 01/02/2017
2/1/2017
2/1/17
2/01/17
02/01/2017
02/1/17
02/1/2017
All the above I need to to convert to 01/02/2017
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER