How to Sort Dates By Month and Day Only on Excel

Updated on January 10, 2017
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 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:

  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.

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.


    0 of 8192 characters used
    Post Comment

    • profile image

      41 hours ago

      Didn't work for me :(

    • profile image

      jaanu 3 weeks ago

      sorry sir i didn't get any help from you.

    • profile image

      jesus Christ 2 months ago

      your a life saver

    • profile image

      NhatChi 6 months ago

      Thanks a lot!

    • profile image

      Huw Pritchard 8 months ago

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

    • profile image

      Mona 9 months ago

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

    • profile image

      fer 10 months ago


    • profile image

      Jeff 10 months ago

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

    • profile image

      Dattatrya kale 10 months ago

      thank you so much

    • profile image

      David 11 months ago

      Thanks, this was very helpful!

    • profile image

      Sanjaya 12 months ago

      Thank you so much.

    • profile image

      Jon M. Luskin, CFP® 12 months ago

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

    • profile image

      Shalini 12 months ago

      Thanks a lot :)

    • profile image

      Angie 17 months ago

      Thank you! So helpful!

    • profile image

      DJD 17 months ago

      Thank's for giving right direction.

    • profile image

      Stark 19 months ago

      Thank's For Sharing.

      Best wishes for you

    • profile image

      Jan 20 months ago

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

    • profile image

      Barbara 20 months ago

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

    • profile image

      Maine 22 months ago

      Lifesaver! .. thanks so much !

    • profile image

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

    • profile image

      David Brenneman 23 months ago


    • profile image

      king Dave 2 years ago

      Wow the above instruction is exellent. thanks so much.

    • profile image

      Mark 2 years ago

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

    • profile image

      Indravadan Shah 2 years ago

      Nice Tips for sorting Birthday with month in Excel

      I have done my job. Thanks again...

    • profile image

      Ben 2 years 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?

    • profile image

      Nadia 3 years ago

      Thanks its really worked

    • profile image

      Raj 3 years ago

      Thanks .. it helps.

    • profile image

      me 4 years ago

      Perfect fix! Thank you!

    • profile image

      Anand 4 years ago

      Thanks For The Solution It's Great

    • profile image

      karen 5 years ago

      exactly what I was looking for! Thank you.

    • profile image

      Alfredo 5 years ago


    • profile image

      Karan 5 years ago

      Superb !!

    • profile image

      DEEk 5 years ago

      Works just fine.Many thanks.

    • profile image

      Anish 5 years ago


    • profile image

      vj 5 years ago

      great thanks

    • profile image

      tilak agrawal 5 years ago

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

    • profile image

      ininotu 5 years ago

      you are the man... thanks for your help

    • profile image

      saumya 5 years ago


    • profile image

      lakshmikanth 5 years ago

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

    • profile image

      Denise 5 years ago

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

    • profile image

      aimless 5 years ago

      thanks! this is great! :-)

    • profile image

      Claire 5 years ago

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

    • profile image

      Ram 5 years ago

      This info helped me.

      It took me just 1 min to implement


    • TheExcelAddict profile image

      TheExcelAddict 5 years ago

      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.

    • profile image

      JJ 5 years ago

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

    • profile image

      daisy 5 years ago

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

    • profile image

      Anju V S 5 years ago

      Thanx a lot.

      Gud Solution

    • profile image

      RathiDevi 5 years ago

      thanks. superb very good solution.

    • profile image

      Rescue Leokeng 6 years ago

      Fantastic just what I was looking for.

    • profile image

      AirS 6 years ago

      Thanks so much

    • profile image

      JimG 6 years ago

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

    • profile image

      nico 6 years ago

      great solution! thank you!

    • profile image

      kyra9 6 years ago

      EXACTLY what I needed! MANY thanks!!!

    • profile image

      Jonathan 6 years ago

      Perfect quick help! Many thanks.

    • profile image

      venkata reddy 6 years ago

      this works :)

      Thanks for sharing

    • profile image

      sainath narayan 6 years ago

      lots of thanks

    • profile image

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


    • profile image

      ryan 7 years ago

      perfect! thanks a ton for sharing!!

    • profile image

      andymack 7 years ago

      Many thanks, exactly what i was looking for.