Sort birthdays by month only in Excel - Full guide

One of the most practical and flexible ways to sort birthdays by month in Excel is to use a helper column. This method uses Excel’s built-in MONTH function to extract just the month value from each date, which can then be sorted independently of the year. This approach is well-suited for users who want visual control and prefer not to use add-ins or macros, and it's compatible with all Excel versions. Note, though, that you’ll temporarily add a new column to your worksheet.

1. In an empty column next to your list of birth dates (assuming your birth dates start in cell B2), enter the formula below into the first cell of the helper column, such as C2:

=MONTH(B2)

This formula extracts the month portion (as a number,1 to 12) from the date in cell B2. If your data starts elsewhere, adjust the cell references accordingly.

2. Drag the AutoFill Handle down to fill the formula for all rows containing dates. This will generate the respective month numbers for each birthday. See screenshot:

3. Select all the cells in the helper column you just created (the month numbers). On the Excel ribbon, go to Data > Sort Smallest to Largest or Sort Largest to Smallest depending on the order you prefer.

4. When prompted with the "Sort Warning" dialog box, ensure you check the Expand the selection option so the entire row (all birthday data) stays together, then click Sort.

Your birthdays are now sorted by month only, disregarding the year information. After confirming the sorting is correct, you can delete or hide the temporary helper column if you wish.

Tips and reminders:

• If your data contains empty rows, Excel will treat these as1/0 during sort, which may lead to confused order—ensure the column is fully populated or filter out blanks.
• When sharing or printing, remember to hide or remove the helper column for a cleaner result.

Sometimes, simply sorting by month is not enough; you might need to organize birthdays by both month and day while completely ignoring the year—such as when preparing an annual celebration calendar. In this case, a different formula approach works well. In an empty cell next to your first date (for example, C2), enter:

=TEXT(B2,"MMDD")

This formula converts the date to a four-digit string representing the month and day ("MMDD" format). Drag the formula down for all rows, then sort as usual on this helper column. See screenshots:

Precautions:

• If you have regional date formats or non-standard dates, verify the helper column result matches the intended dates before sorting.
• After sorting, you may wish to hide or clear this helper column if it is no longer needed.

If you often deal with large lists of birthdays and want a faster, more direct way to sort by month, using Kutools for Excel's Advanced Sort function can save you considerable time. Kutools for Excel enables direct sorting by month, day, or even weekday, without setting up helper columns or formulas.

1. Select the range you want to sort by month, then go to Kutools Plus > Advanced Sort from the ribbon. See screenshot:

2. In the Advanced Sort dialog, specify the birthday column under Column, choose Month from the Sort On drop-down list, and select the desired sort order (ascending or descending). Click OK to apply the sorting. See screenshot:

The birthdays are now quickly sorted only by month. No additional columns are required, making this solution especially suitable for users who regularly manage such tasks or prefer not to adjust the spreadsheet’s structure.

Notes:

• If your birthday data includes duplicate entries or blanks, review the sorted results to confirm all data matches up as intended.
• Kutools for Excel supports batch sorting and many other advanced filters that can further enhance your daily Excel workflow.

For advanced users, or anyone wanting to automate the sorting of birthdays by month without using helper columns or third-party add-ins, a VBA macro can be an efficient and flexible solution. This is especially valuable if you regularly update or import new lists and want a one-click action for sorting.

Applicable scenario: Directly sorts the selected range of dates by month without adding extra columns. Recommended for intermediate Excel users or anyone who wants to speed up repeated tasks.

Limitations: Running this macro modifies the sequence based on the month only. It ignores day and year, so if you need a finer order (month and day), see further solutions below.

1. Click Developer Tools > Visual Basic. In the VBA editor window that opens, choose Insert > Module, then paste the following code into the module:

Sub SortByMonthOnly()
    Dim rng As Range
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim sortCol As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    ' Prompt user for the range
    Set rng = Application.Selection
    Set rng = Application.InputBox("Please select the range with birth dates to sort by month:", xTitleId, rng.Address, Type:=8)
    
    If rng Is Nothing Then Exit Sub
    
    Set ws = rng.Worksheet
    lastRow = rng.Rows.Count + rng.Row - 1
    sortCol = rng.Columns(1).Address(False, False)
    
    ' Add a temporary helper column
    ws.Columns(rng.Columns(rng.Columns.Count).Column + 1).Insert
    ws.Cells(rng.Row, rng.Columns(rng.Columns.Count).Column + 1).Value = "MonthTmp"
    
    ws.Range(ws.Cells(rng.Row + 1, rng.Columns(rng.Columns.Count).Column + 1), _
             ws.Cells(lastRow, rng.Columns(rng.Columns.Count).Column + 1)).FormulaR1C1 = _
             "=MONTH(" & sortCol & rng.Row & ")"
             
    ws.Range(ws.Cells(rng.Row + 1, rng.Columns(rng.Columns.Count).Column + 1), _
             ws.Cells(lastRow, rng.Columns(rng.Columns.Count).Column + 1)).Formula = _
             "=MONTH(" & ws.Cells(rng.Row, rng.Columns(1).Column).Address(False, False) & ")"
             
    ws.Range(ws.Cells(rng.Row, rng.Columns(1).Column), _
             ws.Cells(lastRow, rng.Columns(rng.Columns.Count).Column + 1)).Sort _
             Key1:=ws.Cells(rng.Row, rng.Columns(rng.Columns.Count).Column + 1), _
             Order1:=xlAscending, Header:=xlYes
    
    ws.Columns(rng.Columns(rng.Columns.Count).Column + 1).Delete
End Sub

2. To execute the code: In the VBA window, click the Run button. A dialog will prompt you to select the birthday range. Choose your date cells and confirm. The macro will automatically sort the range based on the month of each date.

Pitfalls & tips:

• This VBA only sorts the selected date column, leaving the data in adjacent columns unchanged.
• Back up your data before running VBA scripts, as actions are often irreversible.
• If you receive an error about unqualified references, check that you have only the date column selected—if your data includes headers, include them in your selection.
• This macro temporarily adds and removes a helper column; if formatting is critical, review columns after execution.

If you need precise ordering of birthdays by both month and day (ignoring year), use this formula in a helper column to create a sortable value combining both:

1. In an empty column alongside your birthday list (e.g., cell C2), enter the following formula:

=MONTH(B2)&TEXT(DAY(B2),"00")

This formula extracts the month as a number and the day as a two-digit string, concatenating them (e.g., February 5 will become "205"). This ensures birthdays are sorted first by month, then days within each month, regardless of the year. Adjust the reference (B2) as necessary for your data location.

2. After entering the formula, press Enter. Then, drag the formula down to fill all rows. With the new helper column filled, sort the data by this column (using Data > Sort). Your birthday list will now be organized by month and day accurately.

Practical advice:

• If birthdays include single-digit days, the TEXT(DAY(B2),"00") ensures "01", "02", ..., so the sorting is always correct.
• If needed, hide or clear the helper column after sorting to keep your worksheet tidy.

Sort cells by last character or number in Excel

Sort full names by last name in Excel

Auto sort column by value in Excel

Save custom sort criteria/order in Excel

Filter dates by month with ignoring year