How to Sort by Date in Google Sheets

Let’s say you work in School, and the school administration wants to organize a special birthday celebration for all students born in a particular month. They want to sort the student data based on their date of birth to identify which students have birthdays in that specific month.

In you are using Google Sheets, sorting data based on a date column is quite easy, and quick. Google Sheets automatically recognizes date formats, making it easy to sort them.

You can select the column with the dates you want to sort, then go to the “Data” menu, choose “Sort range,” and select the sorting options, such as sorting by date in ascending or descending order.

Let’s understand this with the example, we have discussed above…

Steps to Sort by Date in Google Sheets

• First, select the entire range of data you want to sort. Make sure to include all columns containing student names, dates of birth, and dates of admission.

• Now, go to the “Data” menu and then click of the “Sort Range” to open the sorting options dialog box. After that, click on the “Advanced Range Sorting Options”.

• Now, you will get a pop-up box, where you can choose which column to sort by. First click on the “Data has header row” to tick mark it. This will consider first row as a header row.
• After that choose the one with the birthdates from the drop-down. Then, decide if you want oldest dates first or newest. Click “Sort” to sort.

Now your data is sorted by dates.

Note – If you make a mistake while sorting you can use the “Undo” option (Ctrl + Z or Command + Z) that allows you to revert any changes quickly.

And the same options can also be accessed from the filter. Once you apply filter, click on the filter icon and then from the Sort options you can choose Sort A to Z or Sort Z to A.

This is a quick way to sort data by dates.

And if you want to sort date using multiple columns where you have dates in both columns. Then, best is to use the first method we have discussed by using Sort option from the Data menu.

Using the Sort Function to Sort by Date

To sort by dates, you might not need to change the original data but to get a sorted data on a new range of cell. For this, you can use the SORT function in Google Sheets.

SORT function helps you sort your data in a specific order. You can provide it with a range of cells where you have the data, and it gives you back that data sorted as you specified.

First, add data header to a new range, let’s say, to the cell E1.

Now, in the cell, E1, enter the formula:

=SORT(A2:B31, 2, TRUE)

After that, hit enter to get the newly sorted data.

And here’s how this formula works:

• A2:B31 specifies the range of cells containing your data. It includes columns A to B and rows 2 to 31, where you have student names, dates of birth, and data of admission.
• 2 indicates that you want to sort by the second column, which is the Date of Birth.
• TRUE means you want the data to be sorted in ascending order (from oldest to youngest).

Now let’s change the requirement here, and let’s say you want sort dates based on only months not by dates. Like from Jan to Dec to see whose birthday falls in which month. In this case, you can use a formula like the following:

=SORT(A2:C31, MONTH(B2:B31), TRUE)

See in the above example, we have data sorted based on the months from the dates, instead actual dates.

• A2:C31 is the range of cells where you have the data, including columns A to C and rows 2 to 31.
• MONTH(B2:B31) extracts the month from each date in the Date of Birth column (column B). The MONTH function returns the numeric value of the month (1 for January, 2 for February, etc.).
• TRUE tells the formula that you want the data to be sorted in ascending order based on the extracted month (1 to 12).

Make sure to have valid dates in the data so that SORT and MONTH can work propertly.

Use QUERY Function to Sort by Date

QUERY function allows you to get data from a range using SQL-like syntax, offering powerful capabilities for filtering, sorting, and summarizing data directly within your sheet. Here’s the formula:

=QUERY(A2:C31, "SELECT * ORDER BY B ASC")

• A2:C31: This specifies the range of cells containing your data. It includes columns A to C and rows 2 to 31.
• SELECT * ORDER BY B ASC” tells the function what data to retrieve and how to order it. “SELECT *” means to retrieve all columns of data. ORDER BY B ASC” orders the data by the values in column B (Date of Birth) in ascending order (ASC stands for “ascending”).

Checking Dates Before Sorting

In the methods, we have discussed above, you need to make sure that the dates you have in the data must be valid dates as per Google Sheets. Otherwise, sort option and functions won’t be able to give a correct result.

From this, you can use DATEVALUE function to test if dates in the data are valid or not.

DATEVALUE is useful for validating dates entered as text, converting them into a numerical format recognized as valid dates by the Google Sheets.

Use a Google Script Code to Sort by Date

Following is a Google Apps Script code snippet that sorts the data in the range A1:C31 based on the dates in the Date of Birth column (column B).

function sortDataByDate() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var range = sheet.getRange("A1:C31");
  range.sort({column: 2, ascending: true}); // Sorts by the second column (B) in ascending order
}

To run this code:

Go to Extensions > Apps Script, paste the code into the script editor and then save it. To run this, code click on the  “Run” button on the top of the project editor.