On Mon, 30 May 2005 06:11:18 -0600, JE McGimpsey wrote:

One way, using a macro. It will place the new values in the column to
the right of the originals:

Public Sub ConvertDates()
Dim rCell As Range
For Each rCell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With rCell
If IsDate(.Value) Then
.Offset(0, 1).Value = Format(.Value, "yyyymmdd")
Else
.Offset(0, 1).Value = Right(.Text, 4)
End If
End With
Next rCell
End Sub

Note that this is based on the forms in your data.

If you have something like

01/30

meaning January of 1930, this macro will give an incorrect answer.



Interesting output from your macro.

If the input date is between 1/1/1900-2/28/1900, the output from your macro
will be off by one date.

I imagine this has to do with the infamous year 1900 leap year issue.

In VBA, Day 1 is 12/31/1899 and the FORMAT function acts accordingly.

======================
1/1/1900 18991231
2/28/1900 19000227
2/29/1900 19000228
3/1/1900 19000301
1901 1901
1904 1904
1911 1911
1911 1911
1913 1913
1/5/2004 20040105
Winter 2004 2004
===========================

However, the worksheet function TEXT seems to perform the expected conversion.

Perhaps there is another fix?

=========================
Public Sub ConvertDates()
Dim rCell As Range
For Each rCell In Range("A1:A" & _
Range("A" & Rows.Count).End(xlUp).Row)
With rCell
If IsDate(.Value) Then
.Offset(0, 1).Value =
Application.WorksheetFunction.Text(.Value, "yyyymmdd")
Else
.Offset(0, 1).Value = Right(.Text, 4)
End If
End With
Next rCell
End Sub
========================



--ron