In this article, we’ll show you how to sort by date in three ways so you can choose the best for your data. The process is often simple but complications quickly arise when dates are formatted incorrectly. Therefore, we will run through a few tips to ensure our dates are in the correct format to sort before we start.
In many situations, we will be able to sort our dates straight away but it’s worth checking whether our dates are valid and recognized by Google Sheets before we get started. Dates in an incorrect format are a common source of error in the sorting process and can result in a lot of time wasted.
To sort by date in a Google Sheets spreadsheet, you first have to have a column of dates. This could be dates that you've entered manually, or it could be dates that you've downloaded from a CSV file. Lets get started by verifying our dates are written in a valid date format.
Data Validation
Method 1: DATEVALUE Function
The DATEVALUE function transforms a date input and transforms it into a serial number - but only for valid dates.
If the date given is not valid we will see a VALUE error that lets us know that the date format we entered cannot be passed to a date or time.
Method 2: Data Validation Menu Option
Step 1: Select "Data validation" from the "Data" menu.
Step 2: Select the relevant cell range, ensure the data validation criteria is set to date and press save.
Step 3: Invalid dates will be highlighted with a red triangle in the top right-hand corner of the cell. When clicked on, the error message "Invalid: Input must be a valid date" will be shown.
Now that we have determined whether or not our dates are valid and taken a closer look at those that are returning errors. One way to fix errors if they do arise is by splitting the date and concatenating it in a format that Google Sheets recognizes. We can now move on to sorting them. The SORT RANGE Method is the first (and easiest) method to sort data by date in Google Sheets.
Method 1: SORT RANGE Method
Step 1. Select the cells you would like to sort and click on the Data tab.
Step 2. Go to "sort range" and in our example "sort range by column A (A - Z)".
Step 3. Our dates will now be in ascending order.
If you would prefer your dates to be in descending order it’s a similar process but in step 2 select sort range by column A (Z - A).
Another way of doing this is by using the “Advanced range sorting options” to select the column we are looking to re-order. There will also be an option in the sort range dialog box that allows us to select or deselect "Data has header row" to keep column headings in place while we sort our data.
Method 2: Filtering
Step 1. The same as with the menu option we will highlight our dates and click on the “Data Menu”.
Step 2. Select “Create a filter” from the dropdown.
Step 3. Click on the filter symbol on the right-hand side of the date cell and we will be presented with options to sort the range from (A - Z) or (Z - A). (A - Z) will put our dates in ascending order and (Z - A) descending.
Method 3: SORT Function
Step 1. Type =SORT( where you want your sorted date data to appear.
Step 2. Next, add parameters choose the range of the table you would like to sort, the sort_column, which in our case is the date column, and set the is_ascending parameter to TRUE for ascending order or FALSE for descending.
Step 3. Hit “Enter” and your sorted data table will be created.
All of the methods outlined above will work on a table with multiple columns or just a single column containing dates.
The SORT Function
The SORT function allows us to sort by one or multiple columns at once. (Microsoft Excel uses both SORT and SORTBY which have similar functionality, however SORTBY is not available in Google Sheets.)
SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, ...])
range: This parameter specifies the range of cells we wanted to sort, we can sort a single column or an entire table.
sort_column: This determines which column we would like to sort by. We can specify any column within the data we are sorting.
is_ascending: This binary parameter determines whether we want the data to be output in ascending or descending order. It is also used to arrange lists into alphabetical or reverse alphabetical order both horizontally and vertically.
There are additional parameters that can be added if we want to also sort by other columns. We can use sort_column2 and is_ascending2 if we want to sort by two columns, sort_column3 and is_ascending3 for four and so on. However, in most cases sorting by a single column is sufficient.
Converting Date Formats
If you would like to convert the date format you are using, you can do it using the ARRAYFORMULA function. For example, if you have a column of data that contains dates in the "mm/dd/yyyy" format, you can use the following formula to convert it to the "yyyy-mm-dd" format:
=ARRAYFORMULA(TEXT(A2,"yyyy-mm-dd"))
Both the mm/dd/yyyy and the mm/dd/yyyy are valid date formats but you may need to switch based on the standardized format used in your work environment. A company may receive data in the "yyyy-mm-dd" format but report in the "mm/dd/yyyy" format. In this case, it's important to be able to switch between them.
If you are working with large data sets you may want to freeze the first row or create a header to make it easier to reference the different columns while scrolling. To do this, select the first row and go to “View” on the toolbar and select “Freeze” followed by "1 row".
All three methods, the sort range function, the filter method, and the SORT function will all allow you to sort Google Sheets by date in chronological or reverse-chronological order. It is important to note that these methods only work if the dates are in a proper format that Google Sheets recognizes so it’s important to get this right before starting.
Thank you for reading and we hope our guide has helped with you organize your Google Sheets data. If you have and questions or would like some help sorting data, leave a comment below and we may be able to help!
