Solved Sort excel according to birthday

September 12, 2012 at 03:19:20
Specs: Windows 7
How to arrange a list according to birthday in an excel sheet?

SLno Name rollnumber birthday
1 aaa 45 august 25th
2 ddfg 87 april 2nd
3 poiu 98 january 4th
In the above table i need the list to be updated according to birthday column? expected o/p:

SLno Name rollnumber birthday
1 poiu 98 january 4th
2 ddfg 87 april 2nd
3 aaa 45 august 25th

See More: Sort excel according to birthday

Report •

September 12, 2012 at 06:35:15
✔ Best Answer
First, a couple of posting tips:

1 - Before posting any more example data in this forum, please click on the blue line at the end of this post and read the instructions found via that link. That will make it easier for us to understand the layout of your data.

2 - Please try to post your data exactly as it appears in your spreadsheet, with the exception of confidential data.

For example, I assume your "dates" look more like "August 25th" than "august 25th". Note the capitalization. Depending on what you are trying to do with the data, that could make a big difference.

OK, as for your question...

Excel is not considering your birthdays to be dates because of the "th", "nd", etc. It is considering them to be text, and then simply alphabetizing them when you try to sort them.

You need to fool Excel by converting them into numbers that Excel can sort in the manner you are looking for. You can do that by stripping off the last 2 characters and then using the MONTH and DAY functions.

To strip off the last 2 characters, use:


which will give you August 25, but still as text.

Now you need to extract the Month and Day from that string using the MONTH and DAY functions.

You can do all that in separate cells, piece by piece, e.g.:

          D                   E            F
1    =LEFT(C1,LEN(C1)-2)   =MONTH(D1)   =DAY(D1)
           august 25          8           25

Or you can do it all in one cell:

1   =MONTH(LEFT(C1,LEN(C1)-2)) & " " &DAY(LEFT(C1,LEN(C1)-2))

This will return 8 25 for august 25th

Once you have each birthday set up as a "number" space "number", you can sort your data on that column.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question