Google Sheets has a few, exactly three, functions when it comes to sorting a date column. We can use all of them to perform a sort by date of birth in Google Sheets.
As an employer, you may want to surprise your employees by sending them birthday wishes on one of their most memorable days.
No wonder, such small things can make a huge difference in your relationship with your employees.
I am sure that you may not be able to remember the DOBs of all of your employees. Anyhow, it’s not a fault.
If you use Google Sheets to keep your employee records, then it would be easy for you to find the upcoming birthdays of your employees. How?
Learning how to sort by date in Google Sheets is not enough! Then?
You must know how to sort a date column by month and day only in Google Sheets. Yes! You must exclude the year part/element from the sorting.
In this Google Sheets tutorial, you will get three types of formulas to perform a sort by date of birth in Google Sheets. The formulas are based on SORT, SORTN, and QUERY functions.
Reference (Functions): Google Sheets Function Guide.
Before proceeding further, you should know how to sort a date column in Google Sheets.
How to Perform Sort by Date in Google Sheets
To perform sort by date in Google Sheets, first let’s use the SORT function.
Before going to perform sort by date of birth, let’s try to understand the sort by date formula in Google Sheets.
Sample Data:
By Using SORT
We want to sort the above table by the date column B which contains the local purchase order (LPO) dates.
As you can see, the LPO dates are put not in chronological order.
As per the table, the data range is A1:B6. But I will recommend using A2:B6 or A2:B in sorting. Why?
Avoid using the first row, i.e. A1:B1 which contains the field labels. If you use it, it would mess up your data.
The following formula must be used in row # 2 (eg. C2) which is the starting row of the range.
=sort(A2:B,2,true)I know you may want an explanation to the use of the arguments (syntax usage). Here you go!
A2:B – sort range.
2 – the column containing the dates to sort.
true – sort in A-Z (ascending) order (use false for Z-A [descending] order).
How to Include Field Labels (Header Row) in Sorting?
To include a header row in Sort formulas in Google Sheets, use the header row outside the sort formula.
The Curly Brackets can be come-in-handy here.
={A1:B1; sort(A2:B,2,true)}Remember! The above formula must be keyed in the first row.
Before going to perform a sort by date of birth in Google Sheets, let’s go to the SORTN and QUERY alternative to the above.
By Using SORTN
Actually SORTN is my second favorite function in Google Sheets after Query.
The display_ties_mode makes SORTN a very powerful function in Google Sheets. But unfortunately, I think (maybe I am wrong) there aren’t many takers of this function.
The below SORTN formula to be used in row # 1 (because it includes the header row).
={A1:B1; sortn(A2:B,9^9,1,2,true)}In this formula, just ignore the parameters 9^9, and 1. You can use it as it is in any range for the sort by date purpose.
The rest of the SORTN parts are similar to the SORT formula.
That means;
2 – sort column.
true – sort in ascending order.
Sort by Date Using QUERY in Google Sheets
To sort a date column in ascending or descending order, or we can say in chronological or sequential order, we can use the QUERY function too.
Query Formula Example:
=query(A1:B,"Select A,B where A<>'' order by B asc",1)Unlike true or false in SORT/SORTN, here in Query, we can use asc or desc for the sort order.
To perform a sort by date of birth in Google Sheets, we can use all the above three functions.
We just need to know how to exclude the year portion from the date. I am going to explain that below.
Formulas to Sort by Date of Birth in Google Sheets
All the above exercises were to make you comfortable using different functions to sort a table by a date column.
Now once again I am going to use the above table for the example!
I am changing the field labels (column names) from “Customer” to “Employee”. Also, let’s change the field label “LPO Date” to “Date of Birth”.
In addition to the above changes, I am changing the dates in B2:B.
Here is the sample data after the said modifications.
Sort by date of birth is not to find who is older or younger but to find whose birthday is coming or sorting the birthday by sequential or chronological order.
So in the date of birth sorting, as I have already said, we must ignore the year element.
SORT and SORTN to Exclude Year When Sorting Dates
To exclude year when sorting a date column, we can follow the below tips.
Our table to be sorted based on the date of birth column, which is the 2nd column, right?
Earlier we have used the below formula to sort the 2nd column.
={A1:B1; sort(A2:B,2,true)}Instead of 2,true, we must now use month(B2:B),true,DAY(B2:B),true.
After the above changes, the SORT formula to sort by date of birth will be as below.
={A1:B1;sort(A2:B,month(B2:B),true,DAY(B2:B),true)}What about SORTN?
Needless to say, in SORTN also you should make the same changes. Here is the formula after making those changes.
={A1:B1; sortn(A2:B,9^9,1,month(B2:B),true,DAY(B2:B),true)}Query to Exclude Year When Sorting Dates
We can exclude year when sorting a date column in a Query formula also in Google Sheets.
If we take our earlier Query, replace order by B asc with order by month(B) asc, day(B) asc.
So the Query formula to sort by date of birth in Google Sheets for the above table will be as follows.
=query(A1:B,"Select * where B is not null order by month(B) asc, day(B) asc",1)Sort by Date of Birth and Filter the DOBs
How to find the upcoming birthday of your employee from a table in Google Sheets?
For that no need to sort the date of the birth column in ascending order. We can straightaway use the FILTER function.
=filter(A2:A,month(B2:B)=month(today()))The formula will filter the name(s) of the employees whose DOBs are falling in the current month.
That’s all. Enjoy!
