Google Sheets has really easy tools to use when it comes to sorting data.
And these same tools can also be used when you need to sort by date in Google Sheets.
Since a date is nothing but a number in the backend, when you sort by date, you’re essentially sorting a numerical dataset. However, since Date format can take many different formats, sometimes it becomes a pain to sort by date.
In this tutorial, I will show you different ways to sort by date in Google Sheets, some common issues that you may face when working with a date dataset, and a way to convert any text value to a date value.
So let’s get started!
Sort by Date Using the SORT Function
The easiest way to sort the data in Google Sheets is by using the SORT function.
It takes the original dataset and gives you sorted date data as the output. You can have a single or multiple columns sorting where you can specify the date column to be the one to use for sorting.
Now let me show you a couple of examples using sorting by date using the SOR function.
Suppose you have a dataset as shown below and you want to sort this column based on the dates.
Below is the formula that will give you the resulting data that has been sorted by date:
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 in the given range. Since there is only one column in our example, this value can only be 1
- TRUE – This makes the sorting order ascending
In case you have a dataset with multiple columns (with dates in one column), you can also sort this dataset based in the date using the SORT function.
Suppose you have a dataset as shown below:
The below formula will sort this dataset based on the date column.
When using the SORT function make sure the cells where the output of SORT function will be placed are empty. If there is something in any of the cells, the result would be an error (but Google Sheets is kind enough to tell you why it’s giving an error).
Also, since this is an array formula, you can’t change one element of the array. For example, after using the SORT formula, you can delete or change any of those cells. If you do that, it will remove all the results and show you an error. In case you want to change these cells, convert these cells to values and then make the changes.
Sort by Date Using the SORT Range Functionality
In case you don’t need the resulting data to be dynamic and only need to sort by date once, you can also use the inbuilt sorting functionality in Google Sheets.
Suppose you have a dataset as shown below and you want to sort this data based on the date.
Below are the steps to sort by date:
- Select the data to be sorted
- Click the Data option in the menu
- Click on ‘Sort range’ option
- In the ‘Sort range’ dialog box:
- Select the option Data has header row (in case your data doesn’t have a header row, leave this unchecked)
- Select the column you want to sort by (Date in this example)
- Click on A-Z option (which is ascending order)
- Click on the Sort button
The above steps would sort the data instantly.
Common Issue When Sorting by Date in Google Sheets
The most common issue people face when working with dates in Google Sheets is that the date is not in the proper format.
In Google Sheets, all dates are nothing but numbers in the backend. For example, the date January 01, 2020 is actually a number 43831 in Google Sheets. While you may see it as a date, Google Sheets sees it as a number. All it has done it is format it in a way that it looks like a date.
Since these are numbers being formatted as dates, only if Google Sheets recognizes a format that it can convert into a number, it will consider it a proper date. Everything else is considered a text value.
And if you have such formats that are not valid date formats, you will not be able to sort the data using the SORT formula or the in-built sort functionality.
For example, in the below dataset, all the date formats in column A are valid and Google Sheets will be able to convert this into dates (i.e., the number associated with the date).
But in some cases, you may get a date that is in a format that Google Sheets doesn’t understand.
Below is some example where Google Sheets won’t be able to convert the given date into a date number it recognizes and would consider these as a text value.
If you have such dates in your dataset, the sorting would be incorrect.
To make sure your data is correct, you need to make sure the dates are in the right format.
Below are two ways to find out whether the date is in the right format or not:
Check the Alignment of the Date
By default, all dates are aligned to the right in a cell in Google Sheets, and all text is aligned to the left.
In case you notice that dates are aligned to the left, it’s likely an incorrect format.
Check using DATEVALUE function
If you have a cell that contains a date and you use the DATEVALUE function (with that date cell as the 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 for the date January 01, 2020. But in case there is a format that Google Sheets doesn’t recognize as a date (such as 01.01.2020), the DATEVALUE function gives an error.
So to check the dates in a dataset, quickly use the DATEVALUE function in the adjacent column and check if there is an error. If there is an error, it means the cell has a date in the wrong format.
You may also like the following Google Sheets tutorials: