Google Sheets provides simple tools for sorting data, including sorting by date. Since a date is essentially a number in the background, sorting by date means sorting a set of numerical data. However, because dates can have various formats, sorting by date can become challenging. In this tutorial, I will show you how to sort by date in Google Sheets, common issues you may encounter when dealing with date data, and a method to convert any text value into a date value. So let’s get started!
How to Sort by Date in Google Sheets
Here’s how you can sort your data by date in Google Sheets:
- Select the range of data you want to sort.
- Go to “Data” > “Create a filter”.
- Click on the filter icon.
- Select “Sort A to Z” (ascending order) or “Sort Z to A” (descending order).
Validating Your Data
You need to validate your data to ensure you are using the correct format. Here’s how:
- Select the data.
- Go to “Data” > “Data validation”.
- In the data validation window, click on “Add a rule”.
- Go to “Criteria” and choose “Is a valid date”.
- Check the “Show warning” option and click “Done”.
If there are any invalid dates, you will receive a warning and it’s best to correct them.
Sorting by Date in Google Sheets Using Filters
You can also use filters to sort by date in Google Sheets. Let’s look at our sample sheet example below:
- Select the range of data you want to sort.
- Go to “Data” > “Create a filter”.
- Click on the filter icon.
- Select “Sort A to Z” (ascending order) or “Sort Z to A” (descending order).
Syntax of the SORT Function
The syntax of the SORT function is as follows:
SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, ...])
range: The data to be sorted.sort_column: The column identifier for the values to sort within or outside the selected range. It must be a single column of the same size as the range.is_ascending: Whether the range should be arranged in ascending or descending order. Use TRUE or FALSE, where TRUE sorts in ascending order and FALSE sorts in descending order.sort_column2, is_ascending2 ...[ OPTIONAL ]: Any additional columns to sort.
Take a Course
Do you often find yourself visiting sites like ours? Sometimes, it’s better to take a comprehensive course on Google Sheets so you can solve your own problems. It will save you a lot of time in the long run.
Sorting by Date Using the SORT Function
The simplest way to sort data in Google Sheets is by using the “SORT” function, which sorts the original data and gives you the sorted data as output. You can sort a single column or multiple columns by specifying the date column to use for sorting. The best part of using the “SORT” function is that the result is dynamic. If anything changes in your original data set, the sorted data will automatically adjust to reflect the changes. Let me show you how to sort Google Sheets by date using some examples of sorting by date using the “SORT” function.
Suppose you have a data set like the one shown below, and you want to sort this column based on the dates.
Here’s how to sort a Google Sheet by date for the above data:
- Click on an adjacent empty cell.
- Type the formula “=SORT”.
- Select the range you want to sort, which is from A2 to A9.
- Type the column identifier you are sorting, which is 1 for column A.
- After a comma, specify TRUE if you want to sort in ascending order and FALSE to sort in descending order.
=SORT(A2:A9,1,TRUE)
The above “SORT” function takes three arguments:
A2:A9: This is the range that needs to be sorted.1: This tells the “SORT” function to sort based on the first column within the given range. Since there is only one column in our example, this value can only be 1.TRUE: This indicates sorting in ascending order.
If you have a data set with multiple columns (with dates in one column), you can also sort that data set based on the date using the “SORT” function.
Suppose you have a data set like shown below:
The following formula will sort this data set based on the date column:
=SORT(A2:B9,1,TRUE)
When using the “SORT” function, make sure the cells where the result of the “SORT” function will be placed are empty. If there is something in any of the cells, the result will be an error (but Google Sheets is kind enough to tell you why it’s giving an error). Additionally, since it’s a array formula, you cannot change an element of the array. For example, after using the “SORT” formula, you cannot delete or modify any of those cells. If you do so, it will remove all the results and show an error. If you want to modify those cells, you need to convert those cells to values, and then make the changes.
Common Issue When Sorting by Date in Google Sheets
The most common issue people encounter when working with dates in Google Sheets is that the date is not in the correct format. In Google Sheets, all dates are nothing more than numbers in the background. For example, the date January 1, 2020 is actually the number 43831 in Google Sheets. While you see it as a date, Google Sheets sees it as a number. All it has done is format it to look like a date. Since these are numbers formatted as dates, if Google Sheets recognizes a format that can be converted to a number, it will consider it a valid date. Anything else is considered as a text value. And if you have such formats that are not valid date formats, you won’t be able to sort the data using the “SORT” formula or the built-in sort function. For example, in the data set below, all the date formats in column A are valid, and Google Sheets can convert them into dates (i.e., the number associated with the date). However, sometimes you may get a date in a format that Google Sheets doesn’t understand.
Here are two examples where Google Sheets won’t be able to convert the given date into a recognized date number and will consider these dates as text values. If you have such dates in your data set, the sorting will be incorrect. To ensure your data is correct, you need to make sure the dates are in the correct format. Here are two ways to check if the date is in the correct format or not:
Check Date Alignment
By default, all dates are right-aligned in a Google Sheets cell, and all text is left-aligned. If you notice that the dates are left-aligned, it’s likely an incorrect format.
Check the DATEVALUE Function
If you have a cell that contains a date and you use the DATEVALUE function (with that date cell as an argument), it will give you the numeric value of the date. For example, in the below case, the DATEVALUE function returns 43831, which is the numeric value of the date January 1, 2020. But if there is a format that Google Sheets doesn’t recognize as a date (like 01.01.2020), the DATEVALUE function will return an error. To check dates in a data set, quickly use the DATEVALUE function in the adjacent column and see if there is an error. If there is an error, it means the cell contains a date in the wrong format.
Conclusion
In this article, we’ve shown you how to sort by date in Google Sheets. There are two methods to sort by date in Google Sheets: using the “SORT” function and using the sorting option in the data menu. Sorting data is an essential skill when working with spreadsheets, and knowing how to sort by date will help you organize and analyze your data effectively. So give it a try and see how sorting by date can enhance your data analysis in Google Sheets. To explore more tips and tricks for Google Sheets, check out Crawlan.com. Happy sorting!
