## Friday, February 12, 2010

### Excel Sort Dates by Birthday

Today's post is in response to comment by Donna.

Problem on hand is to sort data based on birthday of a person. If you sort it on the date of birth it has year also and you will not be able to sort them by month and date.

One of the possible solution is to drop the Year and then sort them. You will need to add one more column to your data as sort key and use following formula considering that you have Date of Birth in Cell "B2"

=TEXT(B2,"MMDD") will convert 02-Dec-50 as 1202 and 24-Sep-89 as 0924. Now if you sort your data based on new column "Sort Key" on ascending manner, you will get 24-Sep-89 before 02-Dec-50

You will be able to get this result only if the date of birth in your data is a real date. In case not you will need to convert text date to real dates

Use following formula to know Birthday during current year

=--TEXT(B2,"DD/MMM")

You will get error in case birthday is 29-Feb and current year is not a leap year. I suppose this is correct as person will not have birthday every year.

There are couple of other solutions to the sort dates on birthday , you can share one by way of comments if you know

1. Sir,
Can you help me in converting date into words
such as 05.04.1995 into Fifth, April, Nineteen Ninety five

2. Hi D S Raj

Such conversion is possible through custom function only. Date formatting can not handle year conversion into words.

wtw why do you need such conversion, I have not seen any one using dates like this.

Regards
Yogesh Gupta

3. Hi,
I read the above post,but didn't understand much about the sort key column...I have to sort the entries according to the date & month and not according to the year..so could you please help me?
here's my email id if required- amitfunforlife@gmail.com
Amit.

4. And before you know it, you are an expert. It just takes time and effort. There are so many good books centered around the use of Excel VBA that anyone can learn it.excel dashboard software

5. I think I should follow your blog to learn advanced excel. I provide homework writing help to college students, and I think advanced excel skills will definitely help me organize my clients’ data better. I am subscribing to your blog. I hope to see more of such good content in the future as well.

6. 검증된 업체 먹튀검증 최고의 메이져