WPS Spreadsheet: Sort Dates by Month and Day Without the Year
Question details
The user needs to sort employee birthday or anniversary dates by month and day while ignoring the year, without adding helper columns to the source worksheet.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A worksheet contains real date values, such as dates of birth or employment anniversaries, and the user wants a formula-generated list ordered from January through December and by day within each month.
- Observed behavior
- Normal date sorting includes the year, so dates are not ordered solely by month and day.
Confirm that the date cells contain real spreadsheet dates rather than text, and identify the date column and the complete data range that should be returned in the sorted result.
Use SORTBY with MONTH and DAY to create a month-day date order
Use this formula when you want a sorted result without inserting helper columns or changing the original worksheet data.
SORTBY can sort the complete row range by two calculated keys: the month first and the day second. The formula returns a separate sorted result, while the original data remains unchanged.
Click an empty cell where the sorted list should begin, leaving enough blank cells below and to the right for the formula result to spill.
For data in A2:F100 with the dates in column C, enter =SORTBY(A2:F100,MONTH(C2:C100),1,DAY(C2:C100),1) and press Enter. Replace the ranges with your actual table range and date column.
Check the spilled result to confirm that January dates appear first, dates within each month are ordered by day, and the year no longer affects the order.
Use LET and DATE for a table-based birthday sort
Use this option for a structured employee table when you want a reusable formula with a common year as the sort key.
Sort Birthday and Anniversary Dates in WPS Spreadsheet
WPS Spreadsheet can generate a month-and-day sorted view with SORTBY, MONTH, and DAY, allowing you to ignore the year without adding helper columns. The result keeps each employee row together and can be saved or shared in common spreadsheet formats.
- 1. Open the worksheet: Launch WPS Office, open the spreadsheet, and locate the date column containing birthdays or anniversaries.
- 2. Choose an output cell: Click a blank cell with enough space for the sorted result to spill.
- 3. Enter the sorting formula: Type =SORTBY(A2:F100,MONTH(C2:C100),1,DAY(C2:C100),1), replacing the sample ranges with your full data range and date column.
- 4. Verify and save the result: Review the month-day order, then use File > Save As to save the workbook in .xlsx format if needed.
Frequently Asked Questions
Why does regular date sorting include the year?
Spreadsheet dates are stored as complete serial date values, so normal sorting compares year, month, and day. Use SORTBY with MONTH and DAY as separate sort keys to ignore the year.
What formula sorts a complete range by month and day?
If dates are in C2:C100 and the complete records are in A2:F100, use =SORTBY(A2:F100,MONTH(C2:C100),1,DAY(C2:C100),1). The first range must include all related columns.
Why does the formula return incorrect results for some dates?
The date cells may be stored as text instead of real dates. Select the date range and use the available Convert to Date option, or re-enter the values using a recognized date format before applying SORTBY.
Can this formula physically rearrange the original rows?
No. SORTBY produces a sorted spill range and leaves the source data unchanged. To permanently reorder the source, copy the result and use Paste Special as values, or use the spreadsheet's sort command with suitable sort keys.
How should February 29 birthdays be handled?
The DATE sort-key method uses a common year such as 2024, which includes February 29. Decide whether leap-day birthdays should appear on February 28 or March 1 if your organization uses a special birthday policy.
