Sort Dates in Excel by Month and Day, Not the Year

Formulas & functions · Updated November 20, 2026

To sort dates in Excel by month and day while ignoring the year, add a helper column with a date key such as =DATE(2000,MONTH(A2),DAY(A2)), fill it down, then sort the entire table by that helper column from oldest to newest. The helper gives every date the same year, so January dates come before February dates regardless of their original years.

What sorting by month and day means

Excel normally sorts real dates in chronological order, using the year first, then the month and day. That is right for a timeline, but it is not the order you want for a birthday list, seasonal schedule, or recurring annual event when the year is irrelevant. For example, a date in March 2022 normally comes before a date in January 2025 because 2022 is earlier, even though January should lead in a month-and-day list.

The goal is to create a sort key that keeps the month and day but gives every row the same year. The original date remains unchanged; Excel uses the extra key only to arrange the rows. This distinction matters because changing the original dates would discard information you may need later, such as each person’s birth year or the year an event occurred.

This approach sorts in calendar order: January 1, January 2, and so on through December 31. It does not sort alphabetically by month name, nor does it group dates by year. If the dates are recurring events, the helper column is a practical way to display them in the order they arrive during a typical year.

Create a helper column for the sort key

Suppose the dates are real Excel dates in A2:A100, with a heading in A1. Put a heading such as Sort key in B1. In B2, enter the following formula, then fill it down through the rows containing dates:

With the original date in A2, enter this in B2: =DATE(2000,MONTH(A2),DAY(A2))

The formula extracts the month and day from A2 and constructs a new date in the year 2000. For example, an original date of July 4, 1998, and an original date of July 4, 2024, both produce July 4, 2000, as their helper value. Sorting those helper dates therefore puts them in the same month-and-day position.

To fill the formula down, select B2 and drag the fill handle (the small square at the lower-right corner of the cell) to the last row, or copy B2 and paste into the remaining helper cells. Check that the row references change as expected: B3 should use A3, B4 should use A4, and so forth. If the source is an Excel Table, entering the formula in the first helper cell generally fills the calculated column for the table.

The helper values may display with a year even though the year is only a sorting device. If you want the key to show just month and day, select the helper cells, open Format Cells, choose Custom, and use the format m/d. Number formatting changes how a value appears, not the value Excel sorts on.

Sort the full rows, not just the helper column

Once the helper formula is filled down, sort the records by that column. Keeping each row together is essential: sorting only the helper cells would separate the keys from the people, events, or other details they belong to.

  1. Click a cell inside the data range or Excel Table. Confirm that every record has a date and that the helper column is included.
  2. On Windows, choose Data > Sort. In the Sort dialog, select the helper column in Sort by, choose Values for Sort On, and choose Oldest to Newest for Order.
  3. If Excel asks whether to expand the selection, choose Expand the selection so the other columns move with each date.
  4. If the first row contains headings, make sure My data has headers is selected. Click OK and check a few rows at the top, middle, and bottom.

For a simple range, clicking a cell in the table before opening the Sort dialog helps Excel identify the whole block. If the workbook has blank rows or unrelated data immediately beside the list, select the complete list deliberately before sorting. Excel for Mac has a Data tab and a Sort command as well; the dialog labels and basic process are similar. In Excel for the web, select the range or a cell in the table and use the Data tab’s sort controls, taking care to sort the complete records rather than one isolated column.

Use a formula to create a sorted copy

If you have Microsoft 365 or Excel 2021, SORTBY can produce a sorted copy without rearranging the source list. This is useful when you want to keep the original order intact or show a separate, automatically updated view. The formula spills results into the cells below and to the right, so leave the destination area empty.

With dates in A2:A100 and each full record in A2:C100, enter this formula in an empty cell outside the source range: =SORTBY(A2:C100,MONTH(A2:A100),1,DAY(A2:A100),1,A2:A100,1)

The first sort key is the month number in ascending order, and the second is the day number in ascending order. The final key sorts dates with the same month and day by their original date, from earlier year to later year. The result is a new, ordered copy of columns A through C; it does not change the source data. If your records use more columns, expand the record range while keeping the date range aligned row for row. SORTBY is available in Microsoft 365 and Excel 2021, but not in every older Excel release.

For a one-time sort, the helper-column method works across more Excel versions and is easy to inspect. For a view that should update when source dates change, a dynamic formula can be convenient. If the formula returns an error or a spill warning, check that the source and criteria ranges have matching row counts and that no cells block the output area.

Check that Excel recognizes the values as dates

A cell can look like a date without containing a usable Excel date value. Dates imported from a text file or copied from a website may be stored as text. In that case, MONTH and DAY may return errors, or sorting may put values in an unexpected order. Changing the cell’s number format alone does not convert text into a date; formatting controls display, while the stored value determines how formulas and sorting behave.

Check a questionable cell by changing its number format temporarily or testing a known row. Real Excel dates are stored as serial values and respond to date formatting. Text values may stay unchanged. If the dates are text, convert them using a method suited to their actual format and regional conventions before building the helper column. For consistent text dates, Excel’s Text to Columns feature can help: select the date column, choose Data > Text to Columns, and follow the prompts, selecting an appropriate date order when offered. Make a copy first if the original text must be preserved. A string such as 03/04/2025 can mean March 4 or April 3 depending on its convention, so do not assume the interpretation without checking.

Also look for blank cells, error values, or inconsistent entries within the date range. A helper formula copied into a blank row can create an unhelpful key, and a text entry among valid dates can interrupt the sort. Correct or exclude such rows deliberately rather than treating a surprising result as a sorting problem.

Leap days, ties, and other edge cases

The example uses 2000 as the common year because it is a leap year. That lets February 29 remain a valid date in the helper column. The year 2000 is also a leap year under the Gregorian calendar rules because it is divisible by 400. This detail prevents the helper formula from failing for a leap-day birthday or event. In the sorted list, February 29 falls after February 28 and before March 1.

Two records can have the same month and day but different original years. They receive identical helper keys, so their order relative to each other is not specified by that key alone. If the relative order matters, add the original date as a second sort level in the Sort dialog: choose Add Level, select the original date column, and sort it oldest to newest. The SORTBY example above includes the original date as a final tie-breaker.

If date cells include times, the helper formula intentionally ignores them because MONTH and DAY extract only the calendar date components. Thus, two appointments on the same date receive the same key regardless of time. To sort appointments by time within each month-and-day group, include the original date-time column as a secondary key; its time component can then order appointments on that date.

Choose the method that fits your workbook

Use the helper column when you need to sort the original records, want the process to work in older versions of Excel, or need a visible key that coworkers can review. It is also the clearest choice when the workbook is shared with people who may not have dynamic-array functions.

Use SORTBY when you want a separate sorted display that recalculates as source dates change, and your Excel version supports it. Keep the source data intact and put the formula somewhere with enough room for its results. If the workbook will be shared with users on older versions, consider providing the helper-column sort instead, since an unsupported function may not calculate for them.

In either method, preserve the actual date column. A common shortcut is to replace dates with month and day text, but that loses the full date information and can create text-sorting or later calculation problems. A helper key or sorted formula gives the desired order without changing the meaning of the original data.

Frequently Asked Questions

Can I sort birthdays by month and day without changing their birth years?
Yes. Use a helper column based on MONTH and DAY, then sort all rows by that helper. The original birth dates stay in place as values; only the row order changes.

Why does Excel sort January 2025 after March 2024?
Excel is sorting complete dates chronologically, so it compares the year before the month. Create a month-and-day sort key to make the year irrelevant to the primary order.

Does formatting a date as m/d make Excel ignore the year?
No. A custom display format hides the year on screen but does not remove it from the stored date or change chronological sorting. Use a helper key or SORTBY criteria to sort by month and day.

Will the formula work for February 29?
Yes, when the helper formula uses the year 2000, as in the example. It is a leap year, so February 29 remains a valid helper date and sorts between February 28 and March 1.

Can I sort the list without adding a helper column?
With Microsoft 365 or Excel 2021, SORTBY can return a sorted copy using month and day as sort keys. For older versions, a helper column is the straightforward built-in option.

Why does the helper formula return an error?
Check that the source cell contains a real Excel date rather than text, and that the formula references the correct date column. Convert imported text dates to real dates using the correct date order before filling the helper formula down.

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.