How to Sort by Date in Google Sheets (using Formula)

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 how 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 “Google Sheet sort by date” 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.

The best part about using the SORT function is that your result is dynamic. In case something changes in your original dataset, the resulting sorted data will automatically adjust to reflect the changes.

Now let me show you a couple of examples using sorting by date using the SORT function.

Suppose you have a dataset as shown below and you want to sort this column based on the dates.

Dataset for sorting dates in Google Sheets

Here’s how to organize a Google Sheet by date for the above data:

=SORT(A2:A9,1,TRUE)

SORT formula to sort by date in Google Sheets

The above SORT function takes three arguments:

  1. A2:A9 – This is the range that needs to be sorted
  2. 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
  3. 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:

Sort Multiple Columns by date in Google Sheets

The below formula will sort this dataset based on the date column.

=SORT(A2:B9,1,TRUE)

SORT formula when you have multiple columns

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.

spreadsheet fundamentals

Related Reading: Sort by Color in Google Sheets

Sort by Date Using the SORT Range Functionality

If 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.

Sort Multiple Columns by date in Google Sheets

Here’s how to organize by date in Google Sheets:

  1. Select the data to be sorted
  2. Click the Data option in the menuClick on the Data tab in the Menu
  3. Click on ‘Sort range’ optionClick on Sort Range
  4. 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)Sort Range dialog box
  5. Click on the Sort buttonClick the SOrt Button

The above steps would sort the data instantly.

Note: In case you may need the original dataset, it’s best to create a backup copy of the dataset and then perform the sorting.

How to Sort by Date in Google Sheets Using Column Filters

sometimes you may want to sort a single column below is how to sort a column by date in Google Sheets but it also works if you select a whole range too.

 

Sort by Column X option

Once you have ensured all of your data in a particular column are valid dates (Data > Data validation > Date) and frozen the header rows, you can also use the Sort sheet by column X option to alphabetize and sort the sheet by date. Here’s how to sort Google spreadsheet by date:

  1. Select an empty row in the column with the dates in it
  2. Navigate to Data > Sort sheet by column X
    • A -> Z is the earliest date to the latest
    • Z -> A is the latest to earliest

You can also sort by multiple columns if you wish.

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.

Dates in text format are aligned to the left

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.

Date format with DateValue function result

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.

Sort Your Spreadsheets by Date in Google Sheets

Sort spreadsheets by date

Here’s how to sort Google Sheets by date. You must click the A-Z logo on the Sheets main screen and select Last Modified.

Frequently Asked Questions

Why Is My Google Sheet Not Sorting by Date?

Google Sheets does not sort data by default as there are many use cases where people don’t want their spreadsheet sorted by date. You must tell it to sort by date by highlighting the data and heading to Data > Sort range.

How Do I Sort by Date in Google Sheets 2022? / How Do I Sort Google Sheets From Oldest to Newest?

Highlight the range you want to be sorted by date and navigate to Data > Sort Range.

How Do I Sort by Date in Google Drive?

Click the Last modified header above your files and select any of the options that pop up.

Wrapping up How to Arrange Google Sheets by Date

Now that you know how to sort by date in Google Sheets You may also like the following tutorials:

Most Popular Posts

Sumit

Sumit

Google Sheets and Microsoft Excel Expert.

6 thoughts on “How to Sort by Date in Google Sheets (using Formula)”

  1. I found one problem I was having was that you need to have the dates to the right of the names. For example, I had products I have sold in the first column with the dates in the column on the right in the second column. When using this function, it will sort your data but sort alphabetically based on the product name but not by date. I solved this problem by moving the dates into the first column and having product names in the second column. Also, I didn’t heed your warning when you said the dates needed to be formatted properly in date form, very important!

    Reply
  2. HI thanks for the tutorial. I want to sort dates horizontally in the month for my personal budget. I cant fig out how to do this automatically? so column A, line 2 is Dec1,Line 3 is Dec 2 and so on.. thanks so much!!

    Reply
    • Once you’ve sorted your data vertically. You can use the TRANSPOSE function to move a vertical table into a horizontal one.

      Reply
  3. This is very helpful! Thank you for publishing it.

    One question… when I sort dates across 4 years worth of data, it groups all the Januaries together, Februaries together, etc. So I have a list of data for January 2018, January 2019, January 2020, February 2018, February 2019, February 2020, and so on.

    How do I get it to sort chronologically?

    Reply

Leave a Comment