How to Display and Sort Excel Dates as MM/DD Without a Year
Question details
The user wants to format dates to display only the month and day while being able to sort them in correct chronological order regardless of the year.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Formatting date columns for birthdays, anniversaries, or recurring annual events where the specific year is irrelevant but chronological sorting is necessary.
- Observed behavior
- Excel retains the underlying year in its serial date value even when formatted as mm/dd. Consequently, standard sorting operations prioritize the hidden year, disrupting the intended month and day sequence.
Verify whether your current dates are stored as valid Excel date serial numbers or as plain text, as this will determine which formatting and sorting method will work best for your data.
Use Custom Format and Helper Columns for Sorting
Hide the year visually using Excel's Custom Format feature, and extract the month and day into separate columns to achieve accurate sorting.
Because Excel cannot store a true date without a year, visually hiding the year is only half the solution. To sort your data accurately by month and day, you must evaluate those specific components using separate helper columns.
Select the cells containing your dates. Right-click and choose 'Format Cells' (or press Ctrl+1 on your keyboard). Go to the 'Number' tab, select 'Custom' from the Category list, type 'mm/dd' into the Type box, and click OK.
Insert two new columns next to your date column. Name the first one 'Month' and the second one 'Day'.
Assuming your first date is in cell A2, type '=MONTH(A2)' in the Month column and '=DAY(A2)' in the Day column. Drag the fill handle down to apply these formulas to the rest of your data.
Highlight your entire data range, navigate to the 'Data' tab on the ribbon, and click 'Sort'. Add a primary sort level for 'Month' (Smallest to Largest), click 'Add Level', and set the secondary sort for 'Day' (Smallest to Largest). Click OK.
Store Dates as Text Values
Enter your dates purely as text to bypass Excel's underlying serial date and year values entirely.
Format and Sort Dates Easily with WPS Spreadsheet
WPS Office offers a comprehensive spreadsheet application that allows you to easily format dates as MM/DD and accurately sort massive datasets with its intuitive multi-level sorting features.
- 1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing your date values.
- 2. Apply Custom Date Format: Highlight your dates, press Ctrl+1 to open Format Cells, choose 'Custom', type 'mm/dd', and click OK to hide the year visually.
- 3. Extract Month and Day: Use the =MONTH() and =DAY() functions in adjacent columns to pull the specific numerical values out of your date cells.
- 4. Perform Multi-level Sort: Select your dataset, navigate to the Data tab, select 'Sort', and organize your data primarily by the Month column and secondarily by the Day column.
Frequently Asked Questions
Why does Excel show a random 5-digit number when I change my date to Text format?
Excel fundamentally stores dates as serial numbers counting the days elapsed since January 1, 1900. When you convert an existing date cell into a Text format, Excel drops the date styling and reveals this underlying serial number.
Is it possible to permanently delete the year from an Excel date?
No, Excel requires a year, a month, and a day to recognize an entry as a valid date. The only ways to 'remove' the year are to visually hide it using custom formatting (mm/dd) or to convert the date entirely into a text string.
Can I use dd/mm instead of mm/dd for custom formatting?
Yes. The custom format you use should match your regional preferences. If you prefer to see the day before the month, simply type 'dd/mm' into the Custom formatting Type box instead. The helper column sorting method will still work the exact same way.
