If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sorting by dates
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? |
#2
|
|||
|
|||
Sorting by dates
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. "Asking a question" Asking a wrote in message ... 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? |
Thread Tools | |
Display Modes | |
|
|