Excel中按月份和年份排序MM-dd-YYYY格式日期的方法

Hey there! I’ve run into this exact problem before—when Excel treats your dates as plain text instead of actual date values, sorting by year (and month) goes completely haywire. Let’s break down how to fix this and get your data sorted properly.

Why the Sorting Fails #

The root issue is that Excel is reading your "MM-dd-YYYY" entries as text strings, not date values. When sorting text, it only looks at the character order (so "01-01-2017" comes before "12-31-2016" because "0" < "1"), which completely ignores the year logic you care about.

Step 1: Convert Text Dates to Real Date Values #

You have two reliable ways to turn those text strings into Excel-recognizable dates:

Method 1: Use Text to Columns (Fastest Option) #

• Select the entire column of "dates" that are actually text.
• Go to the Data tab → click Text to Columns.
  • Step 1: Choose "Delimited" → click Next.
  • Step 2: Uncheck all delimiter options (Tab, Semicolon, Comma, etc.) → click Next.
  • Step 3: Under "Column data format", select "Date" → from the dropdown, pick MDY (this matches your "Month-Day-Year" format) → click Finish.

Boom—your entries are now real date values, and Excel will recognize the year, month, and day properly.

Method 2: Use the DATEVALUE Formula #

If you prefer working with formulas, add a blank column next to your date text column. In the first cell of the new column, enter:

=DATEVALUE(A1)

(Replace A1 with the cell containing your first text date.)

• Drag the fill handle down to apply this formula to all rows.
• Once the new column is populated, copy it → right-click the original date column → select "Paste Special" → choose "Values" to replace the text with real dates. You can delete the formula column afterward if you don’t need it.

Step 2: Sort by Year and Month Correctly #

Now that your dates are properly formatted:

• Select your entire data range (include headers if you have them).
• Go to the Data tab → click Sort.
  • Set "Sort by" to your date column.
  • Choose "Cell Values" as the sort basis.
  • Pick "Ascending" (oldest to newest) or "Descending" (newest to oldest) as the order.
• Click OK—your data will now sort correctly by year first, then month, then day.

Quick Check to Confirm #

To make sure Excel recognizes your dates as dates:

• Select a few date cells. Look at the Excel status bar (bottom of the window). If it shows values like "Average" or "Sum" (for dates, this will be a serial number), they’re real dates. If it only shows "Count", they’re still text—go back and re-run the conversion step.

内容的提问来源于stack exchange，提问作者MasterSinge