Guide on How To Sort Google Sheets By Date

Admin Jul 21 • 22 min read

In Google Sheets, you not only use tools to organize your data, but also when you need to sort by date. Below CouponUpto will guide you How To Sort Google Sheets By Date.

How To Sort Google Sheets By Date With The “Sort” Function

The best way to sort data by date in Google Sheets is to use the SORT function. The advantage of using this function is that it gives dynamic results. In the situation that the original dataset has any changes, the resulting sorted data will automatically adjust to reflect those changes.

Below is an example for you to sort the data table by date.

Here's the formula that will give you the result for How to sort Google Sheets by date:

=SORT(A2:A9,1,TRUE)

The SORT function above takes three arguments:

A2:A9 – This is the range to be sorted
1 – To request the SORT function to sort based on the first column in the given range. Since there is only one column in this example this value can only be 1
TRUE – This command to select ascending sort order

In case you have a data table with many columns, it is also possible to sort by date using the SORT function.

Suppose you have a Google Sheets data table as shown below:

The formula below will sort this table of data based on the date column.

=SORT(A2:B9,1,TRUE)

When using the SORT function, make sure the output cell of the SORT function is empty. If there are any values in the cell, an error will be returned and Google Sheets will tell you why the error is reported.

Also, since this is an array formula, you cannot change an element of the array.

How To Sort Google Sheets By Date With A Custom Sort Function

In case you don't need the result data to be dynamic and only need to sort by date once, you can also use the built-in sorting functionality in Google Sheets.

Suppose you have a data table as shown below and want to sort this data by date.

Guide on How To Sort Google Sheets By Date Using A Custom Sort Function

Step 1: Select the data need to be

Step 2: Access the Data menu on the toolbar

Step 3: Pick the Sort range option appearing at the drop-down menu

Step 4: In the Sort range dialog box displayed, you will have the following options:

+ Select the option Data has header row (in case your data does not have a header row, skip this option)

+ Select the column you want to sort by date

+ Choose the A > Z option (ascending order)

Step 5: Select the "Soft" button

The steps above will sort your Google Sheets data table instantly by date.

Common Issues of Sorting By Date In Google Sheets

The most common problem that users usually encounter when working with dates in Google Sheets is that dates don't be in the right format.

In Google Sheets, all dates are nothing but numbers. For example, January 1, 2020 is actually a number 43831 in Google Sheets. While you can think of it as a date, Google Sheets sees it as a number. All it did was format it in such a way that it looked like a date.

Since these are numbers formatted as dates, only when Google Sheets recognizes the format it can convert to numbers will it consider it an appropriate date. Everything else is treated as a text value.

If you have valid date formats, you will not be able to sort the data using SORT formulas or other available sorting functions. For example: In the Google Sheets data table below, all date formats in column A are valid and Google Sheets can convert this to a date.

In several cases, you can create wrong date formats and Google Sheets doesn't understand them.

Here are some examples where Google Sheets couldn't recognize the incorrect date format and convert it to a number. You will not be able to sort data in Google Sheets by date if malformed dates appear in your dataset. If you want to check whether you dates are in correct format, here are two ways for you:

Check Sort By Date In Google Sheets

All dates in Google Sheets are default aligned to the right of a cell, and all texts are aligned to the left.

In case you recognise that the dates of your dataset are aligned to the left, it could be a wrong format.

How To Check Sort By Date In Google Sheets Using Datevalue Function

If you have a cell containing a date and use the DATEVALUE function (with a date cell as an argument), it will give you the numeric value of the date.

In this data table below, the DATEVALUE function returns 43831 – which is the numeric value for January 1, 2020. But if Google Sheets doesn't recognize a date data as a date (such as 01.01. 2020 ), the DATEVALUE function will give an error.

Therefore, users can use the DATEVALUE function in the adjacent column to check the date in the data table if there is an error. If there is any error, it means the cell has a wrong date format.