Question for : Bernie Deitrick! Imorted Formats

[CharlesF]

Hi Bernie

Thanks for the macro to sort out imported data.

There is one problem, though:

The dates are displayed as '11/04/04 or dd/mm/yy

When I run your macro the dates become 11/04/04 or mm/dd/yy

How do I correct this in the macro

 

[Norman Jones]

Hi Charles,

Try adding three lines to Bernie's routine:

Sub MacroForCharles()
Dim myCell As Range
On Error Resume Next
For Each myCell In Selection
myCell.Value = myCell.Text
If IsDate(myCell.Value) Then ' <--- Added
line
myCell.Value = Format(myCell, "dd/mm/yy") ' <--- Added line
End If
' <--- Added line
Next myCell
End Sub

 

[Norman Jones]

Hi Charles,

To avoid possible confusion caused by unintentional line wrap, the
procedure reads:

Sub MacroForCharles()
Dim myCell As Range
On Error Resume Next
For Each myCell In Selection
myCell.Value = myCell.Text
If IsDate(myCell.Value) Then
myCell.Value = Format(myCell, "dd/mm/yy")
End If
Next myCell
End Sub

---
Regards,
Norman

Hi Charles,

Try adding three lines to Bernie's routine:

Sub MacroForCharles()
Dim myCell As Range
On Error Resume Next
For Each myCell In Selection
myCell.Value = myCell.Text
If IsDate(myCell.Value) Then ' <--- Added
line
myCell.Value = Format(myCell, "dd/mm/yy") ' <--- Added line
End If
' <--- Added line
Next myCell
End Sub

 

[CharlesF]

Thanks Norman - easy when you know how