Sorting data by date of birth is a common task in Google Sheets. It’s useful for organizing employee records, managing student lists, or planning birthday reminders. You can achieve this using either a formula or the SORT command. When using the SORT command, you’ll need a helper column.

Sorting by date of birth differs slightly from regular date sorting because you should disregard the year component. Let’s dive straight into the examples.

Sort Data by Date of Birth Using the SORT Menu

Here’s the employee data we’ll use for this example, stored in columns A and B:

Enter the following formula in cell C1:

=ArrayFormula(VSTACK("Helper", IFERROR(TEXT(DATEVALUE(B2:B), "MMDD"))))

This formula returns dates in the MMDD format wherever B2:B contains valid dates. The DATEVALUE function ensures that blank cells return errors instead of defaulting to 1230. The IFERROR function then removes these errors and replaces them with blank cells. Additionally, the VSTACK function adds the label “Helper” in the top row of the range.

To sort the data by date of birth:

1. Select the range A1:C.
2. Click Data > Sort range > Advanced Sorting Options.
3. In the dialog box that appears, check Data has header row.
4. In the drop-down menu, select the field Helper for sorting.
5. Click Sort.

This will sort the data by date of birth in Google Sheets.

Sort Data by Date of Birth Using the SORT Function

One limitation of sorting via the SORT menu is that it alters the source data. If you had a manually entered serial number column before sorting, you can use that column to sort the data in A-Z order and revert it back to its original state.

Using the SORT function avoids this issue as it outputs the result in a new range, leaving the source data untouched.

For the above example, use the following formula to sort the employee data by date of birth:

=SORT(A2:B, IFERROR(TEXT(DATEVALUE(B2:B), "MMDD")), 1)

This formula sorts the range A2:B by IFERROR(TEXT(DATEVALUE(B2:B), "MMDD")) in ascending order.

• IFERROR(TEXT(DATEVALUE(B2:B), "MMDD")) converts the dates to MMDD format.
• Normally, the ARRAYFORMULA function is required for such transformations. However, when used inside SORT, it’s automatically applied.

Additional Tip: Adding a Header Row and Using the QUERY Function

The SORT function doesn’t automatically include the header row from the source data. To retain the header, you can vertically append it using the VSTACK function:

=VSTACK(A1:B1, SORT(A2:B, IFERROR(TEXT(DATEVALUE(B2:B), "MMDD")), 1))

Alternatively, you can use the QUERY function to sort the data by date of birth while retaining the header row:

=QUERY(A1:B, "SELECT * WHERE B IS NOT NULL ORDER BY MONTH(B), DAY(B)", 1)

This formula selects all columns (A1:B), filters out rows with empty date cells, and sorts the date column by month and day in ascending order.

The 1 at the end indicates the presence of a header row. If your data doesn’t have a header row, replace it with 0.

Resources

• Calculating Age from Birthdate (DOB) in Google Sheets
• Filter by Upcoming Birthdays in Google Sheets
• Highlight Upcoming Birthdays in Google Sheets