Skip to Content

How To Sort By Date In Google Sheets And What To Do When It Doesn’t Work

How To Sort By Date In Google Sheets And What To Do When It Doesn’t Work

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:

To sort a range highlight first, then click Data, then Sort Range, then Advanced range sorting options

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.

If Sort Range is greyed you need to select a range first

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.

Click Data has header row if your range includes a header row and then select the column header containing your dates

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:

AB
1NameBirthday
2Simon31/03/1970
3Kelly12/10/1972
4Ron1/31/1964
Result of sort on date range

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:

Is a date field not aligned properly? This could be a sign that Google Sheets isn’t seeing the cell as a 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:

ABC
1NameBirthdayIs Date?
2Simon31/03/1970TRUE
=ISDATE(B2)
3Kelly12/10/1972TRUE
=ISDATE(B3)
4Ron1/31/1964FALSE
=ISDATE(B4)
Use 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.

Setting the right locale can help with date data being imported into the 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.