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.

Comments

    0 of 8192 characters used
    Post Comment

    • profile image

      Bill Dwp 

      2 months ago

      Thank you, you rock!

    • profile image

      Joy Nyathi 

      2 months ago

      AWESOME... thank you

    • profile image

      Julia Bebiem 

      4 months ago

      Amazing!!! Thank you so much

    • profile image

      Johan Roberto Rueda 

      4 months ago

      Thanks

    • profile image

      5 months ago

      Didn't work for me :(

    • profile image

      jaanu 

      6 months ago

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

    • profile image

      jesus Christ 

      8 months ago

      your a life saver

    • profile image

      NhatChi 

      12 months ago

      Thanks a lot!

    • profile image

      Huw Pritchard 

      14 months ago

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

    • profile image

      Mona 

      15 months ago

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

    • profile image

      fer 

      16 months ago

      Thankyouuuuuuu!!!

    • profile image

      Jeff 

      16 months ago

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

    • profile image

      Dattatrya kale 

      16 months ago

      thank you so much

    • profile image

      David 

      17 months ago

      Thanks, this was very helpful!

    • profile image

      Sanjaya 

      18 months ago

      Thank you so much.

    • profile image

      Jon M. Luskin, CFP® 

      18 months ago

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

    • profile image

      Shalini 

      18 months ago

      Thanks a lot :)

    • profile image

      Angie 

      22 months ago

      Thank you! So helpful!

    • profile image

      DJD 

      23 months ago

      Thank's for giving right direction.

    • profile image

      Stark 

      2 years ago

      Thank's For Sharing.

      Best wishes for you

    • profile image

      Jan 

      2 years ago

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

    • profile image

      Barbara 

      2 years ago

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

    • profile image

      Maine 

      2 years ago

      Lifesaver! .. thanks so much !

    • profile image

      Sai 

      2 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 

      2 years ago

      Perfect!

    • 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 

      3 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 

      4 years ago

      Thanks .. it helps.

    • profile image

      me 

      4 years ago

      Perfect fix! Thank you!

    • profile image

      Anand 

      5 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

      Perfect!

    • profile image

      Karan 

      5 years ago

      Superb !!

    • profile image

      DEEk 

      5 years ago

      Works just fine.Many thanks.

    • profile image

      Anish 

      5 years ago

      Awesome

    • profile image

      vj 

      5 years ago

      great thanks

    • profile image

      tilak agrawal 

      5 years ago

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

    • profile image

      ininotu 

      6 years ago

      you are the man... thanks for your help

    • profile image

      saumya 

      6 years ago

      thanks

    • profile image

      lakshmikanth 

      6 years ago

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

    • profile image

      Denise 

      6 years ago

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

    • profile image

      aimless 

      6 years ago

      thanks! this is great! :-)

    • profile image

      Claire 

      6 years ago

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

    • profile image

      Ram 

      6 years ago

      This info helped me.

      It took me just 1 min to implement

      Thanks

    • TheExcelAddict profile imageAUTHOR

      TheExcelAddict 

      6 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 

      6 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 

      6 years ago

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

    • profile image

      Anju V S 

      6 years ago

      Thanx a lot.

      Gud Solution

    • profile image

      RathiDevi 

      6 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 

      7 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 

      7 years ago

      perfect! thanks a ton for sharing!!

    • profile image

      andymack 

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