How to Sort Dates By Month and Day Only on Excel

You can sort by month and date only (without the year) in Excel.
You can sort by month and date only (without the year) in Excel.

Sorting Dates in Excel by Month and Date 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:

  1. May 1, 2013
  2. May 2, 1999
  3. 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:

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

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

Comments 46 comments

andymack 5 years ago

Many thanks, exactly what i was looking for.

ryan 5 years ago

perfect! thanks a ton for sharing!!

vasu456 5 years ago

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!!


sainath narayan 5 years ago

lots of thanks

venkata reddy 5 years ago

this works :)

Thanks for sharing

Jonathan 5 years ago

Perfect quick help! Many thanks.

kyra9 5 years ago

EXACTLY what I needed! MANY thanks!!!

nico 5 years ago

great solution! thank you!

JimG 4 years ago

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

AirS 4 years ago

Thanks so much

Rescue Leokeng 4 years ago

Fantastic just what I was looking for.

RathiDevi 4 years ago

thanks. superb very good solution.

Anju V S 4 years ago

Thanx a lot.

Gud Solution

daisy 4 years ago

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

JJ 4 years ago

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

TheExcelAddict profile image

TheExcelAddict 4 years ago Author

Just change the format code (mmdd) in the TEXT formula to the format you want. For example: m = 2, mm = 02, mmm = Feb, mmmm = February, d = 1, dd = 01, ddd = Thu, dddd = Thursday.

Ram 4 years ago

This info helped me.

It took me just 1 min to implement


Claire 4 years ago

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

aimless 4 years ago

thanks! this is great! :-)

Denise 4 years ago

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

lakshmikanth 4 years ago

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

saumya 4 years ago


ininotu 4 years ago

you are the man... thanks for your help

tilak agrawal 4 years ago

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

vj 4 years ago

great thanks

Anish 4 years ago


DEEk 4 years ago

Works just fine.Many thanks.

Karan 3 years ago

Superb !!

Alfredo 3 years ago


karen 3 years ago

exactly what I was looking for! Thank you.

Anand 3 years ago

Thanks For The Solution It's Great

me 3 years ago

Perfect fix! Thank you!

Raj 2 years ago

Thanks .. it helps.

Nadia 2 years ago

Thanks its really worked

Ben 17 months ago

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?

Indravadan Shah 14 months ago

Nice Tips for sorting Birthday with month in Excel

I have done my job. Thanks again...

Mark 13 months ago

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

king Dave 9 months ago

Wow the above instruction is exellent. thanks so much.

David Brenneman 8 months ago


Sai 7 months ago

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!

Maine 7 months ago

Lifesaver! .. thanks so much !

Barbara 5 months ago

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

Jan 5 months ago

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

Stark 4 months ago

Thank's For Sharing.

Best wishes for you

DJD 2 months ago

Thank's for giving right direction.

Angie 7 weeks ago

Thank you! So helpful!

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.

    Click to Rate This Article