# How to Sort Dates By Month and Day Only on Excel

## Sorting Dates in Excel by Month and Day Only

**Sort Dates By Month And Day (Ignoring Year) (Excel 365, 2013, 2010, 2007, 2003) **

Do you want to learn how to sort dates by month and day only, while ignoring the year? For example:

- May 1, 2013
- May 2, 1999
- May 3, 2005

There may be times when you want to sort dates by month and day while ignoring the year. For example, if you want to group anniversary dates such as clients, birthdays, etc.

By default, when you sort dates in a column, they are sorted by year, month, and day. If there are dates from multiple years and you want your data arranged by month instead while disregarding the year, there is no obvious solution.

Here's the work-around solution I use. There are are other ways to do this (as is the case with most things in Excel) but this one is quick and simple:

- Insert a blank column next to your Date column.
- Assuming your first date is in cell A4, in the blank column, enter the formula: =TEXT(A4,"MMDD")
- Copy this formula down to the bottom of your data.
- Sort your data on this column.

And, done! Your dates should now be sorted by month and day only on your spreadsheet.

If you want to change the format of your origfinal dates (Column A in this example) so that the year isn't showing, you can simply select the dates then press CTRL+1, select the Number tab, click Custom in the categories list and enter a format code in the Type field.

For example: m = 2, mm = 02, mmm = Feb, mmmm = February, d = 1, dd = 01, ddd = Thu, dddd = Thursday. Therefore, 'mmm d' will display Jan 2 instead of 02-Jan-62.

## Popular

## Comments

Thanks a lot!

Yes - any help for dates pre-1900? I'm trying to build an #onthisday spreadsheet

Thanks for sharing. But one more thing what does the word TEXT mean in the formula

Thankyouuuuuuu!!!

This formula is very much appreciated. Thank you for sharing.

thank you so much

Thanks, this was very helpful!

Thank you so much.

Thank you for putting this out. This was extremely helpful. Keep up the great and helpful content!

Thanks a lot :)

Thank you! So helpful!

Thank's for giving right direction.

Thank's For Sharing.

Best wishes for you

You are awesome!! Thank you so much. I've been trying to figure this out for hours.

OMG - EXACTLY what I've been trying to figure out for 2 days! Thank you!

Lifesaver! .. thanks so much !

no...the formula is applied, but the computer still sorts the new column on the basis of year, as it is stored in memory...HELP!

Perfect!

Wow the above instruction is exellent. thanks so much.

Perfect. Always easy when you know how. Now I know.

Nice Tips for sorting Birthday with month in Excel

I have done my job. Thanks again...

When I try to sort this data it forces me to sort AtoZ instead of "smallest to largest" like I would think it should for numbers. What am I doing wrong?

Thanks its really worked

Thanks .. it helps.

Perfect fix! Thank you!

Thanks For The Solution It's Great

exactly what I was looking for! Thank you.

Perfect!

Superb !!

Works just fine.Many thanks.

Awesome

great thanks

thanks a ton....very simple way...

you are the man... thanks for your help

thanks

wonderful. thank you for the solution. i was looking exactly for this.

Thank you so much for this simple way!!!!

thanks! this is great! :-)

Thank you so much! This is perfect and so easy :)

This info helped me.

It took me just 1 min to implement

Thanks

Great for sorting, thanks! Is there any way to format the results in that column so they look like a "Month, Day"?

had been looking for a way to do it for long. thanks, it worked!

Thanx a lot.

Gud Solution

thanks. superb very good solution.

Fantastic just what I was looking for.

Thanks so much

how about for dates before 1900, which I guess are not really in DATE format

great solution! thank you!

EXACTLY what I needed! MANY thanks!!!

Perfect quick help! Many thanks.

this works :)

Thanks for sharing

lots of thanks

Dear All,

I am having huage database with Date of birth of each person, Now I want to extract the all members names whose date of birth falls in the specified month from the Drop down list.

I can use filter, which is so easier to done the above thing.. but i want to try in this way..

plz help..

Thanks in Advance!!

VASU

perfect! thanks a ton for sharing!!

Many thanks, exactly what i was looking for.

56