To sort Excel dates by month while ignoring the year, add a helper column with =MONTH(A2), fill it down, and sort the entire table by that column from smallest to largest. Excel’s ordinary date sort is chronological, so it sorts by year first; the helper makes month number the primary sort key instead.
Why a regular date sort puts years first
Excel stores real dates as serial numbers, so each date represents a specific point in time. When you choose an ascending sort on a date column, Excel orders those dates from oldest to newest. That means the year is considered before the month: January 2022 comes before January 2023, and both come before January 2024, followed by later dates in those years.
A month-only order is a different request. It groups every January date together, then every February date, and continues through December, even when the records span several years. Excel’s standard date sort does not offer a built-in “ignore the year” option. You need to tell it which part of each date should control the order.
For example, suppose a list contains dates in January 2022, July 2024, January 2024, and December 2021. A normal ascending date sort puts them in that chronological sequence. A month-based sort puts the two January records first, followed by July, then December.
Use a helper column to sort by month
This method works in Excel for Microsoft 365 on Windows and is also suitable for Excel on Mac and Excel for the web. Assume the dates are in A2:A20 and each row has related information in the other columns. Insert a column called Month, then enter this formula in the first data row:
With the date in A2, enter =MONTH(A2) in the helper cell on row 2. It returns a number from 1 for January through 12 for December.
- Fill the formula down alongside every date. If the data is an Excel table, typing the formula in the first row will usually fill the calculated column automatically.
- Select a cell in the data, then choose Data > Sort.
- If the range has a header row, make sure My data has headers is selected.
- In Sort by, choose the Month helper column. Set Sort On to Values and Order to Smallest to Largest.
- Confirm the sort and check that the full set of columns moved together, keeping each date attached to its original record.
For example, if A2:A7 contains 12/15/2023, 1/8/2022, 7/4/2024, 1/12/2024, 12/2/2021, and 7/9/2022, the helper values are 12, 1, 7, 1, 12, and 7. Sorting by the helper groups the January rows first, then July, then December. The dates within each month may not be in a meaningful order until you add another sort level.
Choose how dates should be ordered within each month
Once month is the primary key, decide what should happen when several records share a month. If you leave the sort at one level, Excel may keep same-month rows in their prior order, but that is not a rule to rely on when a specific sequence matters. Add explicit sort levels to define it.
In the Sort dialog, use Add Level to add another key. For calendar order within each month, sort first by Month from smallest to largest, then by the original Date from oldest to newest. This places dates in the same month in year order, which may be appropriate for an archive. If you want day-of-month order regardless of year, add a Day helper with =DAY(A2) and sort by Month first, then Day from smallest to largest. A row for January 3, for instance, will then appear before January 20 even if the years differ.
You can also use the full date as a later sort level to resolve records that share the same month and day. The right choice depends on the question the list is meant to answer: seasonal patterns, anniversaries, recurring events, and historical records can each call for a different tie-break rule.
Sort a separate result with a formula
If you want a sorted view without rearranging the source records, Excel for Microsoft 365 and Excel 2021 or later can use the dynamic-array function SORTBY. Assume dates are in A2:A20 and related record fields are in A2:C20. Enter this formula in an empty area outside the source data:
With the data in A2:C20 and dates in its first column, enter =SORTBY(A2:C20,MONTH(A2:A20),1). The formula returns the rows in ascending month-number order, with all three columns kept together.
The results spill into the cells below and to the right, so the output area must be clear. The formula does not alter the original range. If you need month and day order, use =SORTBY(A2:C20,MONTH(A2:A20),1,DAY(A2:A20),1,A2:A20,1). With the same layout, this sorts by month, then day of month, then full date as a tie-breaker. If Excel displays serial numbers in the output date column, apply a date number format to that output column. To keep a permanent, editable sorted list, copy the results and use Paste Special > Values in a destination range.
Why month names and text dates can cause trouble
A helper column containing month names can look more readable, but text sorts alphabetically: April comes before February. Use the numeric result of MONTH for a reliable January-to-December order. If you prefer month names for display, keep the numeric helper for sorting and hide it after confirming the results, or apply a custom number format or separate display column.
Another common problem is dates stored as text. A cell may look like a date but fail to behave like one, especially when data was imported or entered using a different regional date convention. In many cases, changing the cell’s number format alone will not convert text into a real date. Test a suspect cell with a formula such as =ISNUMBER(A2); a real Excel date normally returns TRUE because it is stored as a number.
If the test returns FALSE, inspect the original data and its day/month order before converting it. Excel’s interpretation of entries such as 03/04/2024 can depend on regional settings: it may mean March 4 or April 3. Use Data > Text to Columns and select the correct date order when that option fits the imported data, or clean and convert the text with a method appropriate to its actual format. Verify several converted dates before sorting. Also check for blanks, errors, or inconsistent entries in the date column, which can affect the outcome.
Keep the records intact and make the sort easy to repeat
The most important practical safeguard is to sort complete records, not just the date or helper column. If Excel offers to expand the selection, choose the option that expands it so names, amounts, notes, and dates remain on the same rows. Sorting only one column can scramble the relationships between fields. Converting a range to an Excel table with Insert > Table can make it easier to manage headers and keep related columns together.
For a one-time sort, the helper-column approach is straightforward and transparent. For a repeatable report where the source order must remain unchanged, SORTBY creates a separate view. For a simple list of dates with no other row data, you can place the month helper next to the dates and sort both columns together. After checking the result, remove the helper only if you no longer need to repeat or audit the sort.
Frequently Asked Questions
Can Excel sort dates by month across different years?
Yes. Add a helper column using MONTH and sort the complete data range by that column. This groups January dates together, February dates together, and so on, regardless of year.
Why does Excel sort by year instead of month?
A real date is a complete date value, not just a month label. An ascending date sort orders dates chronologically, which puts earlier years first. A month helper changes the sort key to the month number.
How do I sort by month and then by day?
Use one helper column with MONTH and another with DAY. In Data > Sort, sort first by Month, then add a level for Day; set both to ascending order.
Can I sort by month name instead of month number?
You can, but ordinary text sorting is alphabetical rather than calendar-based. Use month numbers as the sort key, or set up a custom list for January through December if you specifically need to sort text month names.
Can I keep the original list unchanged?
Yes. In Microsoft 365 or Excel 2021 and later, use SORTBY in an empty area to create a sorted result. The source range stays in place, and the result updates when its source data changes.
Does sorting by month change the dates themselves?
No. A sort changes row order; it does not remove the year or rewrite the date values. The Month helper controls grouping while the original dates remain intact.
Related
- Excel Repair Tools for XLS and XLSX Files: What Works
- Excel Remove Text and Keep Numbers in a Cell
- Excel Remove Numbers From Cells and Keep Text Easily
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Insert Image from URL: Formula, Steps, and FixesTo insert an image from a URL in Microsoft Excel for Microsoft 365, put the direct image address…
- Excel Rule for Dates Before Today: Formula and SetupTo highlight dates before today in Excel for Microsoft 365 on Windows, select the date cells and create…
- Excel Sum If Cell Is Yellow: Practical Ways to Do ItExcel cannot directly sum cells by fill color with SUMIF or another built-in worksheet formula. To total yellow…
- Excel Formula to Extract Unique Values from a ListIn Excel for Microsoft 365, enter =UNIQUE(A2:A20) to extract the distinct values from a list in cells A2:A20.…
- Excel SUM: How to Add Numbers with a FormulaTo add numbers in Excel, select the cell where you want the total, type a SUM formula, and…
- ISERROR in Excel: Check for Errors in Your FormulasIn Excel, use ISERROR to check whether a value or calculation produces an error: enter a formula such…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.