Sorting by dates

[Asking a question]

One column of my table contains dates, in different forms. Some are
01/01/2002, 01/2002 or just 2002. I understand it may separate these dates,
but suddenly it's not sorting most of the table correctly, with 01/01/2002
and 03/03/2003 in the wrong order and other dates thrown in. How do I fix
this?

 

[Doug Robbins - Word MVP]

If the dates are in the first column and your table has a header row, the
following macro will sort them correctly:

Dim sortcolumn As Column
Dim i As Long
Dim ddate As Range
With Selection.Tables(1)
Set sortcolumn = .Columns.Add(.Columns(1))
For i = 2 To .Rows.Count
Set ddate = .Cell(i, 2).Range
ddate.End = ddate.End - 1
If Len(ddate.Text) = 4 Then
.Cell(i, 1).Range.Text = "01/01/" & ddate.Text
ElseIf Len(ddate.Text) = 7 Then
.Cell(i, 1).Range.Text = "01/" & ddate.Text
Else
.Cell(i, 1).Range.Text = ddate.Text
End If
Next i
.SortAscending
.Columns(1).Delete
End With


--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my services on
a paid professional basis.