What it does – The SORT function can be used to sort by date in Google Sheets.
range: The range of the dataset which needs to be sorted by date
sort_column: The column by which to sort the dataset.
is_ascending: The order in which to sort: ascending or descending. 1 for ascending, 0 for descending.
=SORT(A4:B11, 1, 0)
//This sorts the data in the range A4:B11 according to the dates in column 1 of the selected range in descending order, ie, from the latest.
Get the Google Sheets template with formulas here.
Being able to sort by date is important when analyzing data where dates are essential. Google Sheets makes it easy to sort our data by date.
To be able to sort data by date in Google Sheets, it’s essential to be sure that all the dates are in a valid date format first. Then only one can sort data by dates. Therefore, we need to check for the validity of dates in Google Sheets before we start working on it.
Valid date formats in Google Sheets
To be able to sort by date in Google Sheets, one must be able to recognize if the given date is in correct format. That’s why it’s important to check if all the dates have been entered correctly.
In the following example, we have dates written in different ways. All of these can be easily understood by us but not by Google Sheets. Let’s check out which types of entries are valid as per Google Sheets.
The steps to identify valid dates (recognizable by Google Sheets) are as follows:
- Select the empty cell under the Valid/Invalid header
- Begin your function with the ‘=’ sign. Type in the ‘DATEVALUE’. The Google Sheets will prompt this function, press Tab/Enter key to autocomplete. The tooltip guide will appear along with the details.
- Enter the cell address of the Date on the left
- Press Enter and drag this formula down to fill up the remaining part of the column.
- The data fields which have a numeric value are the valid forms of dates that are recognizable by Google Sheets. The fields with Value errors are the ones that Google Sheets fails to recognize. This is because Google Sheets accepts only those formats which can be converted to date values (numerical values). The dates are sorted on the basis of these numerical values.
Before proceeding with this tutorial, make sure that the dates are recognizable by Google Sheets. We will now begin with the part where we sort by date in Google Sheets.
How to Use the Sort function to sort by date in Google Sheets
Using the Sort function is one of the easiest ways to sort by date in Google Sheets. It takes in a range of datasets (dates) and returns sorted dates as the output. The Sort function is dynamic i.e the output changes dynamically with change in the original dataset.
In the following example, we have a spreadsheet containing a column full of birth dates along with names in an unsorted manner. The objective is to sort by date in Google Sheets in ascending order.
The steps are as follows:
- Select the empty cell under the Date Sorted header.
- Begin your function with the ‘=’ sign. Type in the ‘SORT’. The Google Sheets will prompt this function, press Tab/Enter key to autocomplete. The tooltip guide will appear along with the details.
- Enter the parameters as shown in the following code:
=SORT(A4:B11, 1, 1)
- Press Enter and see the results. It should be the same as the following output:
The output gives us the names in sorted order on the basis of their birth dates that have been sorted in ascending order. That’s how we can sort by date in Google Sheets easily and effectively.
Using the Sort range functionality to sort by date in Google Sheets
The Sort range functionality in Google Sheets is also an effective method for sorting by date, but it is not dynamic. There are instances when one doesn’t wish to use the dynamic approach. This method comes in handy for those cases.
We will be using the same dataset as previous one. The step by step approach is as follows:
- Select the range of data to be sorted excluding the header section
- Click the Data option in the menu. Click on ‘Sort range’ option. Then select the advanced range sorting options.
- Select column A as we have to sort by date in this case. Then select A->Z option as we wish to sort data by dates in ascending order. Click on Sort button.
- The selected columns get sorted on the basis of dates sorted in ascending order.
The sort range functionality helps us to sort by date in Google Sheets. The sorting done in this case is in place. If one needs to maintain the original dataset then make sure to create a copy before using this functionality.
We have learned how to sort data by date in Google Sheets using illustrative examples. We have gone through the step-by-step process of dynamic as well as static methods. Now you are all set to use these methods to your advantage.
Frequently asked questions
How do I filter dates by month and year in Google Sheets?
To insert the Auto Filter, select the cell A1 and press the key Ctrl+Shift+L. And filter the data according to the month and year. This is the way we can put the filter by the date field in Google Sheets.
How do I use the date function in Google Sheets?
You will need to change the number format (Format Cells) in order to display a proper date. For example: =DATE(C2,A2,B2) combines the year from cell C2, the month from cell A2, and the day from cell B2 and puts them into one cell as a date.
We have understood how to sort by date in Google Sheets but the journey doesn’t end here. Are you interested in learning more about how much you can succeed with Google Sheets? With so many powerful features of Google Sheets, you can save time and effort.
We have several tutorials that cover tricks and tips in Google Sheets. You can discover them here.
Here are some articles you might be interested in: