Sort Dates in Excel by Month and Day

Formulas & functions · Updated November 20, 2026

To sort Excel dates by month and day while ignoring the year, add a helper column that converts each date to the same reference year, then sort the entire table by that column from oldest to newest. This keeps January dates before February dates even when the original dates come from different years. Before sorting, make sure Excel recognizes the entries as real dates and that each row’s related information stays together.

What You’ll Need

  • A worksheet containing dates, with one date in each row of a single column.
  • Any names or other information that belongs with each date, arranged in the same rows.
  • An empty column for a temporary helper value. You can remove it after sorting if you no longer need it.

The steps below are for Excel for Microsoft 365 on Windows. The same basic method works in Excel for Mac and Excel for the web, although ribbon labels and window layouts can differ slightly. Save a copy of your workbook first if the order is important or difficult to recreate.

Step-by-Step

  1. Check the date column and your rows. Identify the column to sort, and confirm each date is on its own row. For example, if dates are in A2:A100 and each date belongs to a person in column B, row 2’s date and person must remain together. Include all related columns when you sort; sorting just the date column would mismatch the records. If your data is already an Excel table, the table’s sort controls are a convenient option.
  2. Confirm Excel recognizes the values as dates. Select a few cells and look at the formula bar. A valid Excel date usually appears there as a date value, even if the cell has a custom display format. You can also temporarily change a cell’s format using Home > Number Format and choose General; a real date typically displays as a number. Restore the date format afterward if needed. Text that only looks like a date will not reliably work with the date functions in the next step.
  3. Add a helper-column heading. In the empty column beside the dates, enter a heading such as Month-Day Sort. If the dates begin in A2, put the heading in B1 and use B2 for the first helper value. A descriptive heading makes it easier to identify the sort key in Excel’s Sort dialog and prevents confusion with your original dates.
  4. Enter the helper formula. Assuming the original dates are in A2:A100 and the helper column starts in B2, enter =DATE(2000,MONTH(A2),DAY(A2)) in B2. The formula extracts the month and day from A2 and rebuilds them in the year 2000. The year 2000 is a leap year, so this reference date can represent February 29 as well as the other calendar dates. For example, if A2 contains December 31, 2023, the helper value is December 31, 2000; if A3 contains January 5, 1998, its helper value is January 5, 2000.
  5. Fill the formula down through the date records. Select B2 and drag its fill handle, the small square at the lower-right corner of the selection, down to the last row. You can also copy B2, select the remaining helper cells, and paste. Check a few rows: the formula should change its reference for each row, such as referring to A3 in B3. Do not fill the formula into unrelated blank rows if you can avoid it; this keeps the sort range tidy.
  6. Format the helper values so they are easy to inspect. Select the helper cells and use Home > Number Format to choose a date format, such as a format that displays month and day. Formatting changes only how the value looks, not how Excel sorts it. If you prefer not to display the reference year, choose a custom date format that shows only the month and day, or leave the year visible while checking the results. The helper column’s actual values still use 2000, regardless of the displayed format.
  7. Select the complete data range and open Sort. Click a cell inside the data, then go to Data > Sort. If Excel asks whether to expand the selection, choose the option that expands it so the dates, helper values, and all associated columns move together. In the Sort dialog, check that My data has headers is selected if your first row contains headings. If you selected a range yourself, include the header row and every data column, not just the date and helper columns.
  8. Sort by the helper column, ascending. In the Sort dialog, set Sort by to Month-Day Sort (or the heading you entered), Sort On to Cell Values, and Order to Oldest to Newest. Click OK. The rows should now run from January through December, with days in calendar order within each month. For instance, January 5 should come before February 10, and February 29 should appear between February 28 and March 1.
  9. Review the result and decide what to do with the helper. Check several dates near the beginning, middle, and end of the sorted list, and verify that each name or related value still matches its original date. If you want the data to remain sorted this way, you can keep the helper column so the sort order is explainable and repeatable. If you no longer need it, delete the helper column only after confirming the sort is correct. Deleting it does not undo the row order.

On Mac, use the Data tab’s Sort command; the dialog offers the same key choices, though its position or appearance may differ. In Excel for the web, select a cell in the range and use Data > Sort, or use the table’s filter arrow if the data is formatted as a table. In either case, select the whole table or expand the selection when prompted so related cells stay aligned.

Common Mistakes

  • Sorting by the original date instead of the helper. The original dates sort by year first, so a date from 1998 will precede one from 2023 even if their month-and-day order is not what you want. In the Sort dialog, choose the Month-Day Sort helper heading.
  • Sorting only one column. This can separate dates from names, notes, or other values in the same records. Always include the complete rectangular data range, including every column that belongs to the rows.
  • Using dates stored as text. A cell may display something like 3/7/2024 but still contain text, particularly after importing a file or pasting from a website. MONTH and DAY need usable date values. Convert or re-import text dates as real dates before relying on the helper formula.
  • Using a non-leap reference year. If you build a helper date using a year that is not a leap year, February 29 can produce an error or an unintended result. The formula above uses 2000 specifically because it is a leap year.
  • Assuming repeated month-and-day values have a meaningful order. If several records are dated March 4 in different years, the helper values tie. The sort key does not distinguish their original years, so add a second sort level if you want a specific order among those records.
  • Including blank or stray rows without checking the selection. Large ranges with empty rows, totals, or notes can make the result confusing. Select the actual records and their headings, and keep summary rows outside the sorted range.

Tips

  • Keep the original date intact. The helper approach changes the sort key, not the dates shown in your main column. That makes it safer than replacing the original values with month-and-day text.
  • Use a second sort level when needed. To put records with the same month and day in year order, open Data > Sort, sort first by Month-Day Sort in ascending order, click Add Level, then choose the original date column and sort it Oldest to Newest. This yields calendar month-and-day order, with older dates first when the helper values tie.
  • Make the helper formula easy to maintain. If your records are an Excel table, adding the formula in the new table column usually fills it down automatically. When adding later rows, check that the helper formula has been copied into those new records before sorting.
  • Use a visible month-and-day format while checking. A format such as March 4 or Mar 4 makes the intended ordering easier to verify. Do not confuse display formatting with the sort key: the formula’s underlying value determines the order.
  • Keep the helper heading clear. A heading such as Month-Day Sort is more useful than a generic label like Extra. It also makes it less likely you will accidentally choose the original date field in the Sort dialog.

Troubleshooting

The formula returns #VALUE! or another error. Check whether the source cell is a real Excel date. If it came from an imported file, select the date column and try Data > Text to Columns, then follow the prompts and choose the correct date order under the column data format when available. Alternatively, correct the source values using a method suited to their actual format. Do not guess between month/day and day/month: 4/7/2024 can mean different dates under different regional settings. Recheck a few converted dates before filling or sorting.

The dates sort alphabetically or in an unexpected sequence. Confirm that the Sort dialog uses the helper column and Cell Values, with Oldest to Newest selected. If your helper values are text, Excel may sort them as words rather than as dates. Re-enter the formula in a cell and fill it down, then inspect the results; if the formula itself errors, fix the source date values first.

The rows no longer match after sorting. Undo immediately with Ctrl+Z on Windows (Command+Z on Mac) if the sort was the last action. Repeat the sort by selecting the complete data range and choosing Expand the selection if Excel prompts. If the workbook has been saved or other edits have followed, restore a prior copy or version if available, then sort the full range correctly.

February 29 is missing or appears out of place. Check that the original entry is a valid date and that the helper formula uses 2000. Also check that the displayed value has not been entered as text. The resulting helper date should be February 29, 2000, which sorts between February 28 and March 1.

Blank records appear at the top or bottom. Inspect the selected sort range for blank cells and formulas copied into empty rows. Remove those extra rows from the range, or keep blank records outside the data block. For a recurring workflow, use a table that includes only actual records and confirm new rows receive formulas.

Frequently Asked Questions

Can I sort by month and day without changing the original dates?
Yes. The helper formula creates a separate sort key based on each date’s month and day. It does not alter the original date column, so you can keep both columns or delete the helper after the rows have been sorted.

Why not sort the date column directly?
A normal ascending sort uses the complete date, including the year. That is right when you want chronological order, but it does not group dates by calendar month and day across different years. The helper column makes the year the same for every sort key.

What happens when two records have the same month and day?
They receive the same helper value. If you need a consistent order between them, add a second sort level using the original date, a name, or another field that makes sense for your records.

Can I use this for birthdays from different years?
Yes. It is useful for arranging birthdays by calendar order while retaining each person’s full birth date. Sort by the helper, and include names and any other details in the full selected range so each record stays together.

Will the sort update automatically when I change a date?
The helper formula recalculates when a source date changes, but Excel does not necessarily re-sort the rows automatically. Run the sort again when dates are edited or new records are added, and check that the helper formula is present for every record.

Does the method work if my dates include times?
MONTH and DAY extract the calendar month and day from a valid date-time value, so the helper ignores the time. Records with the same month and day will tie; add another sort level, such as the original date-time column, if you also want them arranged by year or time.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.