Sorting Dates in Excel by Month and Day Only
Sort Dates By Month And Day (Ignoring Year)
Do you want to learn how to sort dates by month and day only, while ignoring the year? For example:
- May 1, 1974
- May 2, 1981
- May 3, 1969
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 and ignore the year, there is no obvious solution.
Here's an easy 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:
- Insert a blank column next to your Date column.
- Assuming your first date is in cell A4, in the blank column, enter the formula: =TEXT(A4,"MMDD")
- Copy this formula down to the bottom of your data.
- Sort your data on this column.
That's it ! Your dates are now sorted by month and day only.
If you want to change the format of your original 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 Category 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.
Gar on August 26, 2020:
Hi Mil I know it's way later but I had the same problem I was just getting something like 12DD as my values I figured out this was because my pc is set up in French, So MM was working for mois mois, but DD wasn't working so I put it to JJ for jour jour, maybe your pc is in another language besides English aswell, hope this helps!
Mil on April 14, 2020:
Thank you very much, i used =TEXT(I2;"MMDD") and i get result 01DD 02DD 03DD etc. cant get the dates correct, what am i doing wrong?
Pratik Upadhyay on February 22, 2020:
Thanks ...GOD bless you
Lee on September 20, 2019:
You are awesome! Very easy and it worked! Thanks
pooja on June 28, 2019:
how to arrange birth dates according to month & date in Ascending order
Soma on May 16, 2019:
Thank you so much. It worked!
Sabrina on January 10, 2019:
Yesss, this worked. Thank you very much!
jeroen on October 11, 2018:
make sure you write this; =TEXT(A4;"MMDD") and not with a comma
vaibhav c. on October 04, 2018:
Yonna D. on September 12, 2018:
Thank you thank you thank you!!!
J~Wow on September 05, 2018:
AMAZING! Don't understand the code or formula, but it worked. If I understood it, I might be able to duplicate it :)
Eyn on August 13, 2018:
Thank you so much!!
Himanshu on July 30, 2018:
Thank you so much... I am very glad at this moment..
vinoth on July 26, 2018:
thanku so much
Bill Dwp on May 16, 2018:
Thank you, you rock!
Joy Nyathi on May 09, 2018:
AWESOME... thank you
Julia Bebiem on March 06, 2018:
Amazing!!! Thank you so much
Johan Roberto Rueda on February 20, 2018:
d on January 20, 2018:
Didn't work for me :(
jaanu on December 30, 2017:
sorry sir i didn't get any help from you.
jesus Christ on November 13, 2017:
your a life saver
NhatChi on July 03, 2017:
Thanks a lot!
Huw Pritchard on May 20, 2017:
Yes - any help for dates pre-1900? I'm trying to build an #onthisday spreadsheet
Mona on March 30, 2017:
Thanks for sharing. But one more thing what does the word TEXT mean in the formula
fer on March 08, 2017:
Jeff on March 08, 2017:
This formula is very much appreciated. Thank you for sharing.
Dattatrya kale on March 07, 2017:
thank you so much
David on February 07, 2017:
Thanks, this was very helpful!
Sanjaya on January 18, 2017:
Thank you so much.
Jon M. Luskin, CFP® on January 09, 2017:
Thank you for putting this out. This was extremely helpful. Keep up the great and helpful content!
Shalini on January 09, 2017:
Thanks a lot :)
Angie on August 29, 2016:
Thank you! So helpful!
DJD on August 24, 2016:
Thank's for giving right direction.
Stark on June 08, 2016:
Thank's For Sharing.
Best wishes for you
Jan on May 19, 2016:
You are awesome!! Thank you so much. I've been trying to figure this out for hours.
Barbara on May 17, 2016:
OMG - EXACTLY what I've been trying to figure out for 2 days! Thank you!
Maine on March 23, 2016:
Lifesaver! .. thanks so much !
Sai on March 13, 2016:
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!
David Brenneman on February 04, 2016:
king Dave on January 02, 2016:
Wow the above instruction is exellent. thanks so much.
Mark on September 06, 2015:
Perfect. Always easy when you know how. Now I know.
Indravadan Shah on August 03, 2015:
Nice Tips for sorting Birthday with month in Excel
I have done my job. Thanks again...
Ben on June 01, 2015:
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?
Nadia on September 19, 2014:
Thanks its really worked
Raj on March 28, 2014:
Thanks .. it helps.
me on September 26, 2013:
Perfect fix! Thank you!
Anand on July 06, 2013:
Thanks For The Solution It's Great
karen on January 15, 2013:
exactly what I was looking for! Thank you.
Alfredo on December 31, 2012:
Karan on October 29, 2012:
DEEk on October 03, 2012:
Works just fine.Many thanks.
Anish on September 08, 2012:
vj on August 29, 2012:
tilak agrawal on August 07, 2012:
thanks a ton....very simple way...
ininotu on May 30, 2012:
you are the man... thanks for your help
saumya on May 09, 2012:
lakshmikanth on April 30, 2012:
wonderful. thank you for the solution. i was looking exactly for this.
Denise on April 19, 2012:
Thank you so much for this simple way!!!!
aimless on April 14, 2012:
thanks! this is great! :-)
Claire on March 15, 2012:
Thank you so much! This is perfect and so easy :)
Ram on February 28, 2012:
This info helped me.
It took me just 1 min to implement
TheExcelAddict (author) on February 16, 2012:
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.
JJ on February 15, 2012:
Great for sorting, thanks! Is there any way to format the results in that column so they look like a "Month, Day"?
daisy on February 06, 2012:
had been looking for a way to do it for long. thanks, it worked!
Anju V S on February 02, 2012:
Thanx a lot.
RathiDevi on February 01, 2012:
thanks. superb very good solution.
Rescue Leokeng on January 05, 2012:
Fantastic just what I was looking for.
AirS on December 15, 2011:
Thanks so much
JimG on December 02, 2011:
how about for dates before 1900, which I guess are not really in DATE format
nico on October 04, 2011:
great solution! thank you!
kyra9 on September 27, 2011:
EXACTLY what I needed! MANY thanks!!!
Jonathan on September 23, 2011:
Perfect quick help! Many thanks.
venkata reddy on September 15, 2011:
this works :)
Thanks for sharing
sainath narayan on August 29, 2011:
lots of thanks
vasu456 on April 07, 2011:
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..
Thanks in Advance!!
ryan on November 16, 2010:
perfect! thanks a ton for sharing!!
andymack on November 15, 2010:
Many thanks, exactly what i was looking for.