Excel - Sorting birthdays 1

[Nate1749]

I want to sort birthday's in ascending order, but want it to ignore the year. I read a thread on how to do this in access, but can is there a method of doing it excel?

Currently birthdays are being sorted liked this
10/8/52
3/4/76
1/30/79
2/14/80

and I want them to sort just by the first two, so like this
1/30/79
2/14/80
3/4/76
10/8/52

The only way I can figure out how to do it in excel is to copy the birthday column and paste it in notepad, then delete the year then paste it back into excel; lotus had a built in function just for this.

-nate

 

[WildHare]

Create a (hidden?) column that uses the MONTH function to return just the month number of your date range. Then sort by this column:

A B
------- --------
1| 10/3/34 =Month(A1)
2| 02/1/57 =Month(A2)
3| 7/30/89 =Month(A3)

etc etc...

JMH


If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[RamziSaab]

Nate, the macro below will do what you want...it will first seperate the dates into seperate columns e.g. 14/10/03 will have 14,10,03 all in seperate columns it will then sort them and clear the columns... the code is assuming your data is in column A and that columns B,C,D are empty, if they are not empty insert three columns and delete them later

Sub seperate()
Dim r As Long
Dim myRows As Long
Dim y As Long
Dim yy As Long
Dim strCode As String
Dim strName As String
Dim n As Integer
Dim i As Long

With ThisWorkbook.Worksheets("sheet1&quot
r = .UsedRange.Rows.Count
n = 1
For myRows = 1 To r
n = 1
strName = .Cells(myRows, 1)
For i = 1 To Len(strName)
yy = y
y = InStr(yy + 1, strName, "/&quot
If y = 0 Then
n = n + 1
strCode = Mid(strName, yy + 1, Len(strName))
.Cells(myRows, n) = strCode
Exit For
Else
strCode = Mid(strName, yy + 1, y - (yy + 1))
n = n + 1
.Cells(myRows, n) = strCode
End If
Next i
Next myRows
Columns("A:B&quot.Select
Columns("A&quot.Select
Selection.Sort Key1:=Range("B1&quot, Order1:=xlAscending, Key2:=Range("C1&quot _
, Order2:=xlAscending, Key3:=Range("D1&quot, Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Range("B&quot.ClearContents

End With

End Sub

 

[RamziSaab]

By the way, it sorts first by month, then by date, then by year

 

[ladyck3]

Since I don't know VBA and am not a macro expert, I run Excel without those entities (unless someone here is kind enough to provide a script)... therefore this is the method that I use to sort dates such as Birthdays:

Sorting Birthdays by Month

=TEXT(D2,"mm, dd&quot

1. Insert a column (which you can hide) and insert the formula above.
2. Sort by that column and the birthdays will last
January, February etc.

If you change the mm to mmm you will get the 3 character month abbreviation of course, and mmmm will produce the full month name. You may or may not already know this.... but just in case

Ladyck3

 

[Nate1749]

Thanks for all the replies!

wildhare: I need it to pull the date as well; plus it won't sort.

ramzisaab: your code worked great!

ladyck3: It's having a similiar problem to wildhare's in that it's not sorting on the hidden column. I select the data and click sort and nothing happens, I think it won't sort because it's linked.

-Nate

 

[WildHare]

Nate, my test sorted just fine...One of us did something wrong. You did select the full data range and then used the DATA/SORT menu items,right?

JMH

If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[ladyck3]

I'm not sure what to say, as WildHare said, make sure you highlight ALL of your data and then choose to sort by the column with the formula.

Hope this helps....

Ladyck3