Sorting Dates by Birthday occurance

[JO505]

Given a file of birthdays that produce a list of birthdays for the next 3 months, including the current month, I want to sort the results in order of sequential occurrence. i.e. If I have a list of 7 birthdays, 2 this month, 4 next month and 1 the following month, I would like to have them listed in chronological order for this year only. If I sort a list of dates say:

8/10/2013
8/31/2013
9/14/2013
9/20/2013
10//27/2013

The Sort will be - oldest to newest;

31-Aug
10-Aug
27-Oct
20-Sep
14-Sep

I am looking for;

10-Aug
31-Aug
14-Sept
20-Sept
27-Oct

The next birthday in the list is the 10th of Aug, followed by the 30st of Aug and so forth.

I am not sure how to handle the date sort to achieve this result.

Jim O

[cpwalker]

Hey Jim, I think there are a few solutions to this but here is one. I Inserted a new column after column E and called it "Derived Birthday".

In the new column in cell F2, try entering the following formula

=DATE(2013,MONTH(E2),DAY(E2))

and drag down.

This formula basically sets everyone's birthdate to the same year (2013) so that you can do the sort you want to. One thing to note--this approach doesn't work if someone has a birthday on February 29 in a leapyear.

[daffodil11]

I found another of many possible solutions:

• I used your formula in H to designated qualified birthdays
• used I to rank them with unique values using rank(ref,range)+countif(range,ref)-1
• and J counts backwards from the highest rank to automatically Index and Match

Attachment 258460

[JO505]

cpwalker,

Very nice! I could not think of how to separate days and months in the sort. It never dawned on me to make them all the same year, I suppose making them the same month would do as well. So far no one was born on Feb 29th so we will cross that bridge when we come to it.

dffodil11,

Thanks for your response also. The sort I get from your formula does not quite sort to what I am looking for. It will sort month part fine but the day is not included in the final rank.

The sort becomes;

14-Sep
20-Sep
27-Oct
10-Aug
31-Aug

was looking for;

10-Aug
31-Aug
14-Sep
20-Sep
27-Oct

Thanks again to both for your input.

Jim O