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

Photobucket



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

3 comments:

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

    ReplyDelete
  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

    ReplyDelete
  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
    thanking in advance,
    Amit.

    ReplyDelete