Mehrab Imtiaz
Today we are going to be looking at two separate ways of how we can sort by date in a Google Sheets spreadsheet.
The processes are fairly simple no matter what level of spreadsheet expertise you have.
Validate Your Dates First
First things first. Before we get to sorting, we must validate whether our given dates are just that, dates, otherwise, Google Sheets will be unable to sort them
More often than not, when inputting dates in Google Sheets, the application fails to recognize the input as a date and leaves it as text. This is understandable as there are so many date formats available to us.
To remedy that, Google Sheets provides us with the DATEVALUE function. We all know that in spreadsheet applications, dates are seen as integer values.
The DATEVALUE function reveals the true integer value of the date and gives us an error if it’s not.
The following table shows us a bunch of dates in different formats.
Let’s see what happens when we put them through the DATEVALUE function. In our cell C3:
=DATEVALUE(B3)Drag the fill handle down the column to apply to all cells.
So, if we were to sort this list of dates, we would have gotten an error.
2 Ways to Sort by Date in a Google Sheets Spreadsheet
To show our sorting processes, we will be using the following list of dates.
Let’s check for the validity of the dates with DATEVALUE. You can do this in any column of your worksheet.
With the dates validated, let’s begin.
1. Sort by Date from Toolbar in Google Sheets (Sort Range)
For our first method, we look at Google Sheets’ own sorting function by the name of Sort Range.
Let’s go over it step-by-step.
Step 1: Select the range of dates you want to sort.
Step 2: Navigate to the Data tab to find the Sort Range option.
We have two options available here:
- Sort range by Column B (A to Z): Old to New
- Sort range by Column B (A to Z): New to Old
Step 3: Select your desired sorting format. Our list after selecting option 1.
We have successfully sorted our column of dates in Google Sheets, but in a practical scenario, it is possible that we may have to sort other columns of data alongside it.
Sort Multiple Columns by Date in a Google Spreadsheet
We have updated our worksheet with more columns of data to make it seem more practical.
We aim to sort Employee and Department columns according to their respective Join Dates. Let’s see how it’s done.
Step 1: Select the range of data from the table.
Step 2: From the Data tab, navigate to Advanced range sorting options. See image:
This should open the Sort range options window:
Step 4: Select Sort by: Column B, as column B contains the Dates.
Step 5: We have our A to Z radio button checked as we want our dates to be organized from the oldest to the newest.
Note: We have left the Data has header row unchecked. This is because our selection did not include the header row of the table. If your selection does have the table headers in them, make sure this option is checked.
Step 6: Click Sort.
You can also navigate to the Sort range options window by selecting your range and right-clicking.
Right-Click > View more cell actions > Sort range
Read More: SORT Function for Multiple Columns in Google Sheets
Similar Reading
2. Using the SORT function to Sort by Date
The next method we are going to show uses the SORT function. This method is more dynamic than the last because every time we change the data the sorted list changes along with it.
The SORT function syntax:
SORT(range, sort_column, is_ascending, [sort_column2, is_ascending2, ...])
It is quite easy to understand all of the input fields that this function requires, so let’s try it out on our existing table.
Since it is a function, the output will be on a separate cell, for which we have created a separate section:
Step 1: In a new cell, B17 in our case, open the SORT function, =SORT(
Step 2: Select the range of data from the table. For us it’s B3:D12.
Step 3: Next we input 1 for our sort_column field as our Join Date column is the first column of our selection.
Step 4: For the is_ascending field we input TRUE. Old to new dates.
Step 5: Close parentheses and press ENTER.
=SORT(B3:D12,1,True)
Note: You can add sort conditions for multiple columns with the SORT function.
Extra Tip: Another Validation Check (Alignment)
An easy way to check whether the dates in your dataset are valid or not is by their alignment in the cell.
As dates are treated as numbers, they will naturally be aligned right in a cell. Any other format would be aligned left.
However, we do not recommend this method as it is completely situational.
- A small amount of data to go through.
- The cells are not center aligned.
It is safer to utilize DATEVALUE if you can spare the time.
Final Words
We hope that the two methods we have discussed of how to sort by date in Google Sheets come in handy in your day-to-day tasks.
Feel free to leave a comment with any queries or advice you might have for us.
