Function Problems

How to Sort Excel Dates by Month Regardless of Year

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs to sort a list of dates, such as birthdays or anniversaries, sequentially by month and day without taking the year into account.

Product: Spreadsheet
Device & OS: not provided
Scenario: Organizing a contact list or calendar to view recurring annual events in chronological order across months.
Observed behavior: Default sorting arranges dates strictly by their full value, which includes the year, disrupting month-based chronological organization.

Before you start

Ensure your date column is formatted as standard dates rather than plain text, so the formulas can correctly recognize and extract the month values.

Solution 1Recommended

Sort Dates by Month Using a Helper Column

Extract the month and day into a new column using the TEXT function, then sort your entire table by this new column.

Because standard sorting considers the year, creating a temporary helper column to isolate the month and day is the most reliable way to reorganize your list.

Create a helper column

Add a new column next to your list of dates and label it 'Sort Month'.

Enter the formula

In the first cell of the helper column (e.g., E2 if your dates are in D2), enter the formula `=TEXT(D2, "mmdd")`.

Apply formula to all rows

Press Enter, then click and drag the fill handle at the bottom-right corner of the cell to apply the formula down to the rest of your data.

Sort the data

Select your entire data table (including headers). Go to the 'Data' tab, click 'Sort', and choose to sort by your newly created 'Sort Month' column from Smallest to Largest.

Tip: Once your data is sorted properly, you can hide the helper column by right-clicking the column letter and selecting 'Hide' to keep your spreadsheet looking clean.

Solution 2

Filter Dates to View a Specific Month

If you do not need to sort the entire list but just want to view all dates falling within a specific month, use the built-in Date Filter feature.

Efficient Spreadsheet Management

Easily Sort and Filter Dates with WPS Spreadsheet

WPS Office provides powerful data manipulation tools, fully compatible with Microsoft Excel formulas. You can easily extract date parts, sort columns, and filter information intuitively.

1. Open your file: Launch WPS Office and open your spreadsheet containing the dates.
2. Use the formula: Insert a new column next to your dates and input the `=TEXT(D2, "mmdd")` formula.
3. Access the sort menu: Highlight your data range, click 'Data' in the top ribbon, and choose 'Sort'.
4. Organize data: Set the sort key to your new column to instantly organize all your dates chronologically by month.

100% compatible with Excel formulas like TEXT, MONTH, and DAYUser-friendly interface for complex custom sortingFree alternative to Microsoft OfficeLightweight application with fast performance

Frequently Asked Questions

Can I use the MONTH function to sort dates?

Yes, you can use `=MONTH(D2)` to extract just the month number (1-12) into a helper column. However, using `=TEXT(D2, "mmdd")` is often preferred because it sorts by month and then by day, ensuring birthdays within the same month remain in perfect chronological order.

Why isn't my date formula working?

If the TEXT or MONTH formula returns an error or an incorrect result, your original dates might be formatted as text instead of actual dates. Try selecting the date column, navigating to the 'Data' tab, and using 'Text to Columns' to convert them into recognizable standard date formats.

Can I sort dates by month without adding a helper column?

Standard spreadsheet sorting always relies on the underlying serial number of the date, which inherently includes the year. To sort purely by month and day across different years, a helper column is currently required to extract those specific values.

Excel & Data Problems