How to sort your data by date

Data is only as valuable as its ability to convey insights and patterns, and nothing does that better than a well-organized spreadsheet. Whether you track deadlines, monitor progress over time, or need to make sense of a sea of information, sorting your data makes it more understandable. There are several ways to sort data. You can sort by name, size, date, and more.

Spreadsheet software like Microsoft Excel and Google Sheets offer powerful sorting tools to help you easily rearrange your data. Whether you do your work on a PC or one of the best high-end Chromebooks, this article explores the different methods for sorting a dataset by date in Google Sheets.

The quickest and easiest way to arrange a dataset by date in Google Sheets is to use the sort range functionality. It uses the original dataset to create an output that’s arranged based on the sort date.

Here’s how it works:

1. Open the sheet with the data to be sorted on your Windows laptop or Chromebook.
2. Highlight the relevant data fields. This includes dates, names, and other data alongside their headings. However, do not include the serial numbers.
3. Click the Data option in the Sheets menu bar.
4. Hover your cursor over Sort range.
5. Choose Advanced range sorting options. A sort range dialog box appears.

6. Select the checkbox next to Data has header row if you highlighted the headings. Otherwise, leave this unchecked.
7. Go to the box beside Sort by and select the sort column (which is Date in this example).

8. Select A→Z to sort in ascending order (earliest to latest) or Z→A to sort in descending order (latest to earliest).
9. Click the Sort button.

Your data is arranged instantly using the date column.

Sort by date using the sort function

The sort range method is a great option for sorting data once. However, if you frequently modify values on your sheet, you’ll need to redo the sorting process every time. The sort function is the better choice if you want the sorted data to be dynamic. In other words, if you change something in the original dataset, the sorted data automatically updates to reflect it. Plus, the sort function is useful for both single and multiple columns.

Here’s how to use the sort function:

1. Open the sheet with the dataset to be sorted.
2. Place your cursor in an empty column to be populated by the sorted data. This is because the sort function creates new fields instead of modifying the fields with your original data. You can also label the new columns as we’ve done below.

3. Enter the =SORT(A1:B5,1,TRUE) formula in the function (fx) bar at the top of the page, where A1:B5 represents the sort range, 1 represents the sort column (the first column in a dataset), and TRUE denotes an ascending order of sorting.

4. We use the =SORT(B2:C6,2,TRUE) formula for this illustration. B2:C6 excludes the serial number column and header rows, 2 represents the sort (date) column, the second of the two selected columns, and TRUE for ascending order.
5. Press the Enter key on your keyboard.

The selected data sorts automatically, populating new cells starting from the cursor position. While this seems more daunting than the sort range method, there are many reasons to justify using it. For starters, it preserves your original data column. So you can always go back to it if necessary.

The significant advantage of the sort formula is that it auto-updates the sorted data when the original is changed. For example, changing Stephen Schenck’s date from 3/5/2021 to 12/5/2021 automatically takes his name from the top of the sorted field to the bottom.

Another advantage of the sort function is its ability to organize future data. Suppose we want to add 10 more values in our example of five names and dates. We would replace the C6 in the function with C16 to give =SORT(B2:C16,2,TRUE). Then, when we add a new name and date, it’s automatically arranged into the sort field as long as we don’t exceed C16.

For the sort function to work, the output cells (where the sorted data will be placed) must be empty. Otherwise, it returns an error. Sheets prioritizes the data you type over the sort function, so the latter cannot override the former. Similarly, typing anything in any output cell removes the existing results and displays an error, as in the screenshot below.

Check the validity of the date format

For the date sort function to work, Sheets needs to recognize your values. If you have invalid dates (in an unsupported format), the algorithm reads them as plain text, giving incorrect results. Here’s how to confirm that your dates are in the proper format.

Check the alignment

In Google Sheets, the text is aligned to the left. However, numbers do not follow this rule by default and are assigned to the right. This remains the case, even if the figures contain special characters like $ for currencies, / for dates, and : for time, as long as they are recognized.

If your dates are aligned to the left, they’re likely in an incorrect format. Generally, typing the date in full (for example, August 3, 2021 or 3 August 2021) is valid. But if you use only figures, demarcate the numbers using a slash or hyphen in the following format: MM/DD/YYYY or MM-DD-YYYY.

Use the DATEVALUE function

Checking if your dates are right-aligned is usually enough to know if they’re valid. But if the data spans the cell width, it can be challenging to discern the alignment. In that case, use the DATEVALUE function in the adjacent columns to check for errors and get the numeric value of the dates.

Set your cursor in an empty cell, input the DATEVALUE function, =DATEVALUE(C2:C12), or whatever range contains your valid dates, and press Enter.

In our example, the function returns the value 44470 for the date 10/1/2021 and a #VALUE! error for the invalid 03.03.2021.

Sort your Google sheets like a pro

Organizing data is crucial, especially when dealing with large volumes from diverse sources. Sorting data using fields with numbers, dates, times, and positions can make it easier to comprehend and extract meaningful insights. While this article talks about sorting data by date, you can use several other parameters. For example, here’s an article where we discuss sorting plain text in alphabetical order.