How to Sort Dates by Month and Day in Excel

Sort_Dates_Excel_3The new chairperson of your “Staff Party Planning Team” bounces in your office Monday morning. You learn that plans are underway to begin celebrating employees’ birthdays and the team needs a list of all staff. No problem. A quick download from the payroll system yields an alphabetical list of names and birthdays:

Sort_Dates_Excel_1

A quick re-sort to put in chronological and you’ll be done:

Sort_Dates_Excel_2

Well, not so fast. The chronological sort uses the full date and thus, places the list in order from oldest employee to youngest! What the team is looking for is to sort dates based on month and day only, such that all January birthdays would be listed first, then February, and so on.

Simple formulas will accomplish the goal. The functions MONTH(date) and DAY(date) will extract the month and day numbers to columns D  and E.

Sort_Dates_Excel_4

Sort_Dates_Excel_5

At last we are ready to sort! Select rows 1 through 8 and click on the down arrow on the Sort & Filter option in the Editing group on the Ribbon. In the drop down, click on Custom Sort.

Sort_Dates_Excel_7Because we included Row 1 in the selection, the box “My data has headers” should be checked. Had we selected Rows 2 through 8, Row 2 contains data rather than headers, so we would uncheck that box.

Including the header row makes selecting the sort levels very straightforward. Here we select Month as the first level and Day as the second level. Then click OK.

Sort_Dates_Excel_7

The sort is complete and now your Party Planning Team can be ready for each month’s celebrations. Let the good times roll!

Sort_Dates_Excel_8

 

Leave a Reply

Your email address will not be published. Required fields are marked *