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


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.

    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 could you please help me?
    here's my email id if required-
    thanking in advance,

  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. This is a great resource for sorting dates by birthdays. The Best Merchant Cash Advance Leads service is an incredibly useful tool for businesses that rely on merchant cash advances to help them manage their cash flow. The sorting feature makes it easy to identify customers who need to be contacted for payment and to track payments more quickly and efficiently.


Learn how to create Excel dashboards.
Yogesh Gupta's Excel Tips