Date Format Issue

[alexm999]

I have a file that exports a date in the following format:
2003.12.30
2004.5.11

Its the year, month and then day. BUt I cannot use cell formating t
change the date format to:
Month/Day/Year

How can I do this using programming.
The data is always in column

 

[Chip Pearson]

Alex,

Try something like the following:

Sub AAA()
Dim Y As Integer
Dim M As Integer
Dim D As Integer
Dim Rng As Range
Dim Arr As Variant
For Each Rng In Range("D110") '<<<CHANGE
If Rng.Value <> "" Then
Arr = Split(Rng.Text, ".")
Y = Arr(LBound(Arr))
M = Arr(LBound(Arr) + 1)
D = Arr(LBound(Arr) + 2)
Rng.Value = DateSerial(Y, M, D)
End If
Next Rng
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com