How do you sort a range containing dates in Google Sheets?
Sorting a data set in columns or rows containing date data in Google Sheets can easily be done by highlighting the range and then clicking on Data > Sort Range > Advanced range sorting options , as shown below:
If the Sort Range option is greyed out it means you haven’t selected a range. Check you have a range highlighted as shown above where I’ve selected cells A1 to B4.
Once you click the Advanced sorting range options you’ll see a modal window that will help to determine how you would like to sort your data.
To sort the range that contains dates you need to select the column that contains the date values. As I highlighted my range with the header row I need to tick the box Data has header row , if I don’t select this then the sort will include the header row instead of leaving it where it is.
Once you’ve ticked that box you can then select from the Sort by drop-down menu the header label containing the dates you want to sort.
If your data range didn’t contain a header row then select the column containing your date data.
Finally, before clicking on the Sort button the last option available in your sort operation is determining how you want to sort the data range. The two options available are ascending or descending .
The option labelled A → Z will sort your dates in ascending fashion, meaning it will put the oldest date first and the date furthermost into the future last. Conversely, selecting the option Z → A will sort dates in descending future, meaning the date furthermost into the future will be first and the oldest date in the past will be last.
Once you’ve determined your sorting method click on the Sort button.
With the running example used my data set would look as follows upon clicking sort:
| A | B | |
|---|---|---|
| 1 | Name | Birthday |
| 2 | Simon | 31/03/1970 |
| 3 | Kelly | 12/10/1972 |
| 4 | Ron | 1/31/1964 |
As you can see from the outcome of the sorting operation something isn’t quite right. If my sort was to put the birthdays of these people into ascending order then Ron should have been first not last.
Date Sort Not Working In Google Sheets
When the date sort isn’t working in Google Sheets there could be a couple of issues you need to check.
First, check if the cell is a date field.
One easy way to check if a cell has been recognised by Google as a date field is to see if it’s aligned properly. Have a look at your data set and see if a field isn’t aligned properly, my data set clearly shows that something’s not quite right with Ron’s birth date:
If the column has already been text aligned then this type of approach will not work. Thankfully, there is another option in being able to see if Google has not detected a cell as being a date.
Use
ISDATE()
Function
Another way to determine if Google Sheets has set the type of a cell as date is to use the
ISDATE
function. The function takes a single parameter and simply returns
TRUE
or
FALSE
if the cell is a date or not.
Using our data set here’s how this function would look:
| A | B | C | |
|---|---|---|---|
| 1 | Name | Birthday | Is Date? |
| 2 | Simon | 31/03/1970 |
TRUE
=ISDATE(B2)
|
| 3 | Kelly | 12/10/1972 |
TRUE
=ISDATE(B3)
|
| 4 | Ron | 1/31/1964 |
FALSE
=ISDATE(B4)
|
ISDATE()
function to determine if cell is a date
As you can see from the output of the
ISDATE
function it has detected that Ron’s birthday is not a valid date. Therefore, you would need to amend the cell to make it a valid date.
Check Spreadsheet Settings
If you are using data that is of a different structure to what you normally use within your own locale, for example, in Australia the common format of a date is DD/MM/YYYY whereas in the USA the common date structure is MM/DD/YYYY it may help by setting the spreadsheet to the type of date data used.
To set the spreadsheet setting click on File > Settings which will bring up the following modal window where you can change the Locale setting to represent the type of date data being handled in your spreadsheet.
Summary
Sorting dates within a range can easily be done by using the Data > Sort Range menu option in Google Sheets.
If dates aren’t sorting properly check the cells are dates by looking at the alignment of the cells or by using the
ISDATE
function to determine if cells are dates.
Finally, if you are handling dates that are different to your locale check the spreadsheet settings and change the locale to match. This will make it easier for Google Sheets to detect the type of date data being used.
