To sort Excel dates by calendar month, add a helper column with the month number, then sort the entire data range by that column from smallest to largest. For dates in A2:A100, enter =MONTH(A2) beside the first date, fill the formula down, and use Data > Sort to sort all rows by the helper column; this groups January through December regardless of year.
What sorting dates by month means
Sorting dates by month can mean two different things. You might want to group every January together, every February together, and so on, even when the dates come from different years. Or you might want to put dates in ordinary chronological order, which considers year, month, and day together. Choose the intended result before sorting: a month-only sort deliberately separates the usual year-by-year timeline.
For example, a list containing 15-Aug-2023, 10-Jan-2024, and 03-Jan-2023 becomes January 2024, January 2023, August 2023 if sorted only by month and the original order is retained within each month. To put the January dates in date order within their group, sort by month first and date second. To keep the entire list chronological, sort by the actual date column instead of using a month helper.
Excel dates that it recognizes are stored as numeric date values, even when the cells display names such as Jan or August. A cell’s number format changes how a date looks, not the underlying date value. Sorting by a month number makes the month order explicit and avoids alphabetic sorting of month names.
Sort real dates into calendar-month groups
In Excel for Microsoft 365 on Windows, use a helper column to create a sortable month key. These steps assume each date is in column A and the first data row is row 2:
- Insert a blank column next to the dates and give it a heading such as Month number.
- In the first helper cell, enter
=MONTH(A2). With the date 15-Aug-2024 in A2, the result is 8. - Fill the formula down for every row that has a date. The results run from 1 for January to 12 for December.
- Select a cell in the data, then choose Data > Sort. If Excel asks whether to expand the selection, choose Expand the selection so each row stays together.
- In the Sort dialog, select the Month number column under Sort by, leave Sort On set to Cell Values, and choose Smallest to Largest under Order. Select OK.
The date rows now appear in calendar-month groups. Keep the helper column if you expect to sort the data again; otherwise, after confirming the result, you can remove it. If you delete it, choose the helper column and delete the column itself rather than clearing cells in the middle of a table.
When the data is formatted as an Excel table, sorting a table column normally sorts the entire table row, keeping names, amounts, and other related values aligned with each date. For an ordinary range, selecting the full data area and expanding the selection is important. Sorting only the helper cells would detach the month keys from their original records.
Choose how to order dates within each month
A month number creates groups, but it does not by itself specify the order of dates within a group. You can choose the secondary sort based on what the list represents. In the Sort dialog, use Add Level to add another sort criterion beneath Month number.
- Keep dates in order within each month: Add the original date column as the second level and sort it from Oldest to Newest. January dates from different years will then be ordered by year within the January group.
- Arrange recurring calendar dates by day: Add a Day helper as the second level if you want every first day before every second day, regardless of year. For dates in A2:A100, enter
=DAY(A2)beside the first date and fill down. For 15-Aug-2024, the result is 15. - Put years in a chosen order within each month: Add a Year helper or the date column as a secondary level, depending on whether day order also matters.
If you use a Year helper, with dates in A2:A100, enter =YEAR(A2) in the first helper cell and fill down. For 15-Aug-2024, the result is 2024. Sorting by month and then year groups the same month together and orders those groups by year; it does not necessarily order individual days unless you add a further criterion.
Sort levels are applied from top to bottom. For example, Month number first and date second gives month groups, with dates in chronological order within each group. Date first and Month number second is usually redundant because the first level already sorts complete dates chronologically.
Use month names or a custom month order
If your data contains month names as text rather than complete dates, a normal A-to-Z sort puts April before August and December before February. That is alphabetical order, not calendar order. For a short list containing names such as January, February, and March, Excel’s custom lists can provide a calendar sequence instead of alphabetic order.
On Windows, open Data > Sort, select the month-name column, and open the Order list. Choose Custom List, select or enter the month sequence, and confirm it. Excel includes month-name lists in its built-in custom lists in standard installations. The wording and location of the custom-list controls can vary by Excel build. If the list is not available, use a helper column containing month numbers and sort by that column.
When month names are part of a larger dataset, a numeric helper is often easier to audit than a custom sort order. It also makes the intended ordering visible to other people opening the workbook. If text values are inconsistent, such as Jan, January, and JANUARY appearing in the same column, standardize them before relying on a custom list.
Text that looks like a date is another case to check. If cells contain text strings rather than Excel date values, date formatting or a month formula may not work as expected. Convert the text into real dates before sorting when possible; the Text to Columns tool or a careful import with the correct regional date order may help. Check a few converted values before applying changes to the whole column, because 03/04/2024 can mean different dates under different regional conventions.
Sort chronologically instead of grouping by month
If the goal is simply to arrange dates from earliest to latest, no helper column is needed. Select a date cell and choose Data > Sort Oldest to Newest. For a full dataset with headers, use Data > Sort, confirm that My data has headers is selected when appropriate, and sort by the date column. This keeps years in chronological order, rather than collecting all Januaries into one group.
Changing the display format to show only a month does not change this behavior. For instance, if a date displays as August because its number format hides the day and year, Excel still sorts the underlying complete date. If the dates span multiple years, an ascending date sort will place all dates in the earlier year before dates in later years, even though the displayed month names may repeat.
For a recurring annual report, grouping by month can be useful for comparing seasonal patterns. For a transaction ledger, project timeline, or appointment list, chronological order is generally more useful because it preserves when events actually occurred. The right sort is determined by the question the worksheet needs to answer, not by how the dates are formatted.
Common sorting problems and fixes
- Months appear alphabetically: The sort used text labels such as April or August. Sort by a numeric month helper or choose a month custom list.
- Rows no longer match their dates: Only one column was sorted. Undo immediately with Ctrl+Z on Windows, then sort the complete range or table. On Mac, use Command+Z.
- The month formula returns an error or an unexpected result: The source may be text, blank, or an invalid date. Check whether Excel recognizes the cell as a date and verify the import’s regional date order.
- Some dates are at the top or bottom: The range may contain blanks, text, or values outside the selected sort range. Inspect the affected cells and make sure the sort includes all data rows.
- The months are grouped correctly but days look out of order: Month number was the only sort level. Add the full date as a second level, or use a day helper if the goal is day-of-month order across years.
Be cautious with dates stored as text. Applying a date number format alone does not reliably convert text into date values; formatting changes appearance, while conversion changes the cell’s contents. One quick check is to use a helper formula on a known date and see whether Excel returns a month number. If it returns an error, investigate the cell rather than assuming the sort dialog is at fault.
Before sorting a large or important list, consider making a copy of the worksheet. This is especially helpful when the source contains merged cells, blank rows, or irregular blocks of data, all of which can complicate sorting. A clean table with one header row and one record per row is the most reliable structure.
Windows, Mac, and Excel for the web
The helper-column method works across current desktop versions of Excel: use the MONTH function, then sort the complete range by its results. In Excel for Microsoft 365 on Windows, the main route is Data > Sort. In Excel for Mac, use the Data tab’s Sort controls; the dialog’s layout can differ from Windows, but the same sort levels and ascending order are available. On Mac, Command+Z is the usual undo shortcut, compared with Ctrl+Z on Windows.
Excel for the web also supports sorting data, although the ribbon and dialog options can differ from desktop Excel and custom-list controls may not be available in the same way. A helper column with month numbers is a practical fallback: create the month values, select the whole data set, and sort by that column. If the workbook uses a feature or sort order that is difficult to manage in the browser, open it in the desktop app to use the full Sort dialog.
Frequently Asked Questions
How do I sort Excel dates by month name?
For real dates, use a helper column with =MONTH(A2), fill it down, and sort the whole dataset by that column from smallest to largest. This produces calendar order rather than alphabetic month-name order.
Can I sort dates by month and keep the years together?
Yes. Sort first by the month number and then by the date or year, depending on the order you want within each month. If you want the full timeline, sort by the date column alone instead.
Why does Excel sort April before January?
Excel is likely treating the month labels as text and sorting alphabetically. Use numeric month values from the MONTH function, or use a custom list that specifies January through December.
Can I sort by month without adding a visible helper column?
A helper column is the clearest method for a normal in-place sort. You can hide the helper column after sorting, but keep it in the range so the sort can be repeated. Another option is to use a custom month list when the values are month names as text.
Does formatting a date as mmmm sort it by month?
No. A number format such as mmmm changes how a real date is displayed; it does not change the value Excel sorts. To group different years by month, sort by a month-number helper.
Related
- Excel DOS Keyboard Shortcuts: What Actually Works
- Excel Direct Access vs Link: What’s the Difference?
- Excel Desktop Keyboard Shortcuts: A Practical Guide
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- HLOOKUP Examples for Beginners in ExcelHLOOKUP searches across the top row of a table and returns a value from a specified row beneath…
- Excel Dependent Drop-Down Lists with XLOOKUPYes—Excel for Microsoft 365 can use XLOOKUP to return the options for a dependent drop-down, but XLOOKUP does…
- Excel PMT Function: Calculate Loan PaymentsTo calculate a fixed loan payment in Excel, divide the annual interest rate by the number of payments…
- Excel Formula for Checking If a Date Is Before Another DateTo check whether one date is earlier than another in Excel, use the less-than operator: with the date…
- CHAR Excel Usage Examples: Formulas, Codes, and TipsIn Excel, CHAR converts a number from the computer’s character set into a character. For example, with no…
- How to Split Cell Data in Excel: Text to Columns & FormulasTo split cell data in Excel, select the cells, choose Data > Text to Columns, pick Delimited or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.