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

Excel Formula : Sum top 5 in unsorted range

Today I am sharing a formula to sum top 5 values in an unsorted range.

=SUMPRODUCT(LARGE(Data,ROW(1:5)))

See the screen cast below to know secret behind this formula.



Actually we are using array formula to find top 5 values , then summing them to find the sum of top 5 values in unsorted range.

From the screen cast you can see that

1. We are entering this formula using sumproduct, which is shortcut to enter array formulas in excel.
2. Data is a named range A3 to B36
3. We are using excel function Large to find Top values.
4. Row(1:5) - is is shortcut to create an array of numbers {1,2,3,4,5} as Kth position

This leads to find top 5 values in unsorted range and then summing them.

Food for thought :

Just change Row(1:5) to Row(1:10) you will be able to find Top 10 values.

Or Change Large with small to find out bottom 5 values.

Download Excel file with formula to sum top 5 values in an unsorted range

to play furhter with this formula