如何按年、月对mm/yyyy格式的日期列进行排序？

Hey there! The problem you’re facing is really common—when dates are stored as text in mm/yyyy format, tools treat them as plain strings instead of actual date values. That’s why sorting doesn’t work logically (since "01/2018" would come before "11/2017" in string order). Let’s fix this based on what tool you’re using:

For Excel or Google Sheets #

Here’s the step-by-step fix to get your data sorted from 11/2017 to 05/2018:

1. Convert your text dates to actual date values
   • In Excel:
     • If your dates won’t convert directly, use the DATEVALUE function in a new column. For example, if your date is in cell A2, enter =DATEVALUE(A2&"/01")—this adds a day (the 1st) to turn mm/yyyy into a full date Excel can recognize.
     • Then, select the new column, right-click, choose Format Cells, and set it to a date format (you can use a custom format mm/yyyy later if you want to hide the day).
   • In Google Sheets:
     • Use the DATE function to split the month and year. For a date in cell A2, enter =DATE(RIGHT(A2,4), LEFT(A2,FIND("/",A2)-1), 1). This creates a valid date value with the 1st of each month.
2. Sort your data
   • Select your entire dataset (both date and result columns).
   • Use the sort feature, choosing the new date column as the sort key in ascending order.
3. Clean up (optional)
   • If you don’t want to see the day in your date column, reformat it to mm/yyyy (custom format in Excel, or "More formats" → "Custom number format" in Google Sheets).

For Python (If You’re Using Code to Process Data) #

If you’re working with a script (like using pandas), here’s how to sort your data properly:
First, load your data into a DataFrame:

import pandas as pd

# Your raw data
data = {
    "month": ["01/2018", "02/2018", "3/2018", "04/2018", "05/2018", "11/2017", "12/2017"],
    "Result": [96.13636, 96.40000, 94.00000, 97.92857, 95.75000, 98.66667, 97.78947]
}
df = pd.DataFrame(data)

Next, convert the month column from text to datetime values:

# Parse the mm/yyyy strings into datetime objects
df['month'] = pd.to_datetime(df['month'], format='%m/%Y')

Now sort the DataFrame by the month column:

# Sort in ascending order (oldest to newest)
df_sorted = df.sort_values('month')

If you want to convert the dates back to mm/yyyy text format for display:

df_sorted['month'] = df_sorted['month'].dt.strftime('%m/%Y')

Printing df_sorted will give you the exact order you want: from 11/2017 all the way to 05/2018.

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