How to Sort Dates By Month and Day Only on 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:
- May 1, 2013
- May 2, 1999
- 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:
- 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.
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.