Cell PhonesComputersConsumer ElectronicsGraphic Design & Video EditingHome Theater & AudioIndustrial TechnologyInternet

How to Sort Dates By Month and Day Only on Excel

Updated on January 10, 2017

Joined: 7 years agoFollowers: 3Articles: 4
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

    • andymack 6 years ago

      Many thanks, exactly what i was looking for.

    • ryan 6 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 5 years ago

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

    • AirS 5 years ago

      Thanks so much

    • Rescue Leokeng 5 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

      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 4 years ago

      Superb !!

    • Alfredo 4 years ago


    • karen 4 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 19 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 17 months ago

      Nice Tips for sorting Birthday with month in Excel

      I have done my job. Thanks again...

    • Mark 16 months ago

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

    • king Dave 12 months ago

      Wow the above instruction is exellent. thanks so much.

    • David Brenneman 11 months ago


    • Sai 10 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 9 months ago

      Lifesaver! .. thanks so much !

    • Barbara 8 months ago

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

    • Jan 8 months ago

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

    • Stark 7 months ago

      Thank's For Sharing.

      Best wishes for you

    • DJD 4 months ago

      Thank's for giving right direction.

    • Angie 4 months ago

      Thank you! So helpful!

    • Shalini 7 days ago

      Thanks a lot :)

    • Jon M. Luskin, CFP® 7 days ago

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

    Click to Rate This Article