sorting dates in Excel

Question

following the thread: How do I sort dates by month in Excel, regardless of the year?

I have found that the year is peculiar. from the years 1900 to 2026, the equation result does delete the year. But try year 1899 and earlier, and the year appears in the result.

I also found that the format can be mmdd or ddmm. and I even added a dash going for dd-mm.

these all worked. but the year limitation throws me.

Answer 1

To sort dates by month in Excel regardless of the year, you can use a helper column to extract the month from your date values. Here’s how you can do it:

1. Add a Helper Column: Next to your date column, create a new column that extracts the month from each date. You can use the formula =MONTH(A1) where A1 is the cell containing your date. Drag this formula down to fill the rest of the cells in the helper column.
2. Sort by Helper Column: Once you have the month numbers in the helper column, you can sort your data based on this column. Select your data range, go to the Data tab, and choose Sort. In the Sort dialog, select your helper column and sort it in ascending or descending order as needed.
3. Year Limitation: Regarding the year limitation you mentioned, Excel typically interprets dates based on the century cutoff of 2029. Dates entered with years before 1900 may not be recognized properly, which can lead to unexpected results when sorting. For example, dates from 1899 and earlier may show the year in the result due to how Excel handles two-digit year numbers.
4. Date Formats: You can use various date formats like mmdd, ddmm, or dd-mm as you mentioned, but ensure that Excel recognizes them as dates for sorting to work correctly. If Excel treats them as text, sorting might not yield the expected results.

By following these steps, you should be able to sort your dates by month effectively, while being aware of the limitations regarding years before 1900.

---

References:

• How Excel works with two-digit year numbers
• Converting date format - Microsoft Q&A