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.

This article is accurate and true to the best of the author’s knowledge. Content is for informational or entertainment purposes only and does not substitute for personal counsel or professional advice in business, financial, legal, or technical matters.

Questions & Answers

    Comments

      0 of 8192 characters used
      Post Comment
      • profile image

        pooja 

        7 weeks ago

        how to arrange birth dates according to month & date in Ascending order

      • profile image

        Soma 

        3 months ago

        Thank you so much. It worked!

      • profile image

        Sabrina 

        7 months ago

        Yesss, this worked. Thank you very much!

      • profile image

        jeroen 

        10 months ago

        make sure you write this; =TEXT(A4;"MMDD") and not with a comma

      • profile image

        vaibhav c. 

        10 months ago

        It worked,Thanks

      • profile image

        Yonna D. 

        11 months ago

        Thank you thank you thank you!!!

      • profile image

        J~Wow 

        11 months ago

        AMAZING! Don't understand the code or formula, but it worked. If I understood it, I might be able to duplicate it :)

      • profile image

        Eyn 

        12 months ago

        Thank you so much!!

      • profile image

        Himanshu 

        12 months ago

        Thank you so much... I am very glad at this moment..

      • profile image

        vinoth 

        12 months ago

        thanku so much

      • profile image

        Bill Dwp 

        15 months ago

        Thank you, you rock!

      • profile image

        Joy Nyathi 

        15 months ago

        AWESOME... thank you

      • profile image

        Julia Bebiem 

        17 months ago

        Amazing!!! Thank you so much

      • profile image

        Johan Roberto Rueda 

        18 months ago

        Thanks

      • profile image

        19 months ago

        Didn't work for me :(

      • profile image

        jaanu 

        19 months ago

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

      • profile image

        jesus Christ 

        21 months ago

        your a life saver

      • profile image

        NhatChi 

        2 years ago

        Thanks a lot!

      • profile image

        Huw Pritchard 

        2 years ago

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

      • profile image

        Mona 

        2 years ago

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

      • profile image

        fer 

        2 years ago

        Thankyouuuuuuu!!!

      • profile image

        Jeff 

        2 years ago

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

      • profile image

        Dattatrya kale 

        2 years ago

        thank you so much

      • profile image

        David 

        2 years ago

        Thanks, this was very helpful!

      • profile image

        Sanjaya 

        2 years ago

        Thank you so much.

      • profile image

        Jon M. Luskin, CFP® 

        2 years ago

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

      • profile image

        Shalini 

        2 years ago

        Thanks a lot :)

      • profile image

        Angie 

        2 years ago

        Thank you! So helpful!

      • profile image

        DJD 

        2 years ago

        Thank's for giving right direction.

      • profile image

        Stark 

        3 years ago

        Thank's For Sharing.

        Best wishes for you

      • profile image

        Jan 

        3 years ago

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

      • profile image

        Barbara 

        3 years ago

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

      • profile image

        Maine 

        3 years ago

        Lifesaver! .. thanks so much !

      • profile image

        Sai 

        3 years 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 

        3 years ago

        Perfect!

      • profile image

        king Dave 

        3 years ago

        Wow the above instruction is exellent. thanks so much.

      • profile image

        Mark 

        3 years ago

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

      • profile image

        Indravadan Shah 

        4 years ago

        Nice Tips for sorting Birthday with month in Excel

        I have done my job. Thanks again...

      • profile image

        Ben 

        4 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 

        4 years ago

        Thanks its really worked

      • profile image

        Raj 

        5 years ago

        Thanks .. it helps.

      • profile image

        me 

        5 years ago

        Perfect fix! Thank you!

      • profile image

        Anand 

        6 years ago

        Thanks For The Solution It's Great

      • profile image

        karen 

        6 years ago

        exactly what I was looking for! Thank you.

      • profile image

        Alfredo 

        6 years ago

        Perfect!

      • profile image

        Karan 

        6 years ago

        Superb !!

      • profile image

        DEEk 

        6 years ago

        Works just fine.Many thanks.

      • profile image

        Anish 

        6 years ago

        Awesome

      • profile image

        vj 

        6 years ago

        great thanks

      • profile image

        tilak agrawal 

        7 years ago

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

      • profile image

        ininotu 

        7 years ago

        you are the man... thanks for your help

      • profile image

        saumya 

        7 years ago

        thanks

      • profile image

        lakshmikanth 

        7 years ago

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

      • profile image

        Denise 

        7 years ago

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

      • profile image

        aimless 

        7 years ago

        thanks! this is great! :-)

      • profile image

        Claire 

        7 years ago

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

      • profile image

        Ram 

        7 years ago

        This info helped me.

        It took me just 1 min to implement

        Thanks

      • TheExcelAddict profile imageAUTHOR

        TheExcelAddict 

        7 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 

        7 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 

        7 years ago

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

      • profile image

        Anju V S 

        7 years ago

        Thanx a lot.

        Gud Solution

      • profile image

        RathiDevi 

        7 years ago

        thanks. superb very good solution.

      • profile image

        Rescue Leokeng 

        7 years ago

        Fantastic just what I was looking for.

      • profile image

        AirS 

        7 years ago

        Thanks so much

      • profile image

        JimG 

        7 years ago

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

      • profile image

        nico 

        7 years ago

        great solution! thank you!

      • profile image

        kyra9 

        7 years ago

        EXACTLY what I needed! MANY thanks!!!

      • profile image

        Jonathan 

        7 years ago

        Perfect quick help! Many thanks.

      • profile image

        venkata reddy 

        7 years ago

        this works :)

        Thanks for sharing

      • profile image

        sainath narayan 

        7 years ago

        lots of thanks

      • profile image

        vasu456 

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

        VASU

      • profile image

        ryan 

        8 years ago

        perfect! thanks a ton for sharing!!

      • profile image

        andymack 

        8 years ago

        Many thanks, exactly what i was looking for.

      working

      This website uses cookies

      As a user in the EEA, your approval is needed on a few things. To provide a better website experience, turbofuture.com uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

      For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at: https://turbofuture.com/privacy-policy#gdpr

      Show Details
      Necessary
      HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
      LoginThis is necessary to sign in to the HubPages Service.
      Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
      AkismetThis is used to detect comment spam. (Privacy Policy)
      HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
      HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
      Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
      CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
      Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the googleapis.com or gstatic.com domains, for performance and efficiency reasons. (Privacy Policy)
      Features
      Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
      Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
      Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
      Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
      Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
      VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
      PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
      Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
      MavenThis supports the Maven widget and search functionality. (Privacy Policy)
      Marketing
      Google AdSenseThis is an ad network. (Privacy Policy)
      Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
      Index ExchangeThis is an ad network. (Privacy Policy)
      SovrnThis is an ad network. (Privacy Policy)
      Facebook AdsThis is an ad network. (Privacy Policy)
      Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
      AppNexusThis is an ad network. (Privacy Policy)
      OpenxThis is an ad network. (Privacy Policy)
      Rubicon ProjectThis is an ad network. (Privacy Policy)
      TripleLiftThis is an ad network. (Privacy Policy)
      Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
      Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
      Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
      Statistics
      Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
      ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
      Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)
      ClickscoThis is a data management platform studying reader behavior (Privacy Policy)