Sorting Google sheets data by date

Piyush Gupta | Manager, Product Growth
Reading Time: 5 minutes
Sorting in Google Sheet

For the ones who are always working online and prefer Excel sheets on the web, Google sheets are the ultimate solution. A team of multiple people can simultaneously work on the same sheet and update their contributions. However, this feature of combined working also possesses a challenge of its own. 

Whether the data is complex or not, it is always complicated to read unorganized sets of data. And that is why sorting becomes a helpful and mandatory tool. Let’s find out more about it. 

Why is sorting important? 

Sorting becomes helpful as well as essential to read and interpret the humongous data. It can be presented in an order that makes more sense for a dedicated task. To keep with this statement, you can sort by date, by text, by number, and even in ascending or descending order. All of these sorting mechanisms have been implemented for your ease in Google Sheets. 

Working with dates in Google Sheets 

For humans, keeping notes of their days and time is extremely crucial. Undoubtedly, making dates is a critical measurement of when a task-related entry was made or updated. 

The same has been managed well by Google sheets. This product uses the 1900 calendar date system and has numerous inbuilt functions to handle it. It even has a date function created wherein you can specify the month, date and year for any occurrence. 

Wondering why we mentioned this? Because dates are crucial and can only be read in a dedicated format In Google sheets. But for the world we know, there are several formats to present it. So the creator came up with a function to ease the readability and store the dates in a single decided format. 

Surprisingly, Google sheets relies on this method and decides if a particular date is valid or not. Although, it takes other steps as well, such as keeping them right-aligned and enhancing readability. But certainly, that was not enough. So, next time you come across the dilemma on how to validate the date format, simply refer to the function below: 

DATE (year, month, day)

How to sort data by date? 

Now that sorting and date function is well acknowledged, let’s find out how to work with the two simultaneously. 

Method 1: Using the sort range method 

Step 1: Create a Google sheet and populate it with relevant data. If you are new to this, we suggest you create a minimum of 2 columns – for date and next is say weekday for instance. 

Sorting Raw Data

Step 2: Once your sheet is populated, hover over the ‘Data’ tab of the menu bar. 

Step 3: Once you click on the ‘Data’ tab, a list of ready-made options appears. Click on the ‘Sort range’ option. 

Step 4: A new pop-up window will open. In that, select the alphabetical column identifier for your date column. For example, if you’ve stored dates in column A, then A will become the alphabetical column identifier, and you need to select ‘A’ in this pop-up window. 

Sorting option in Google Sheet

Step 5: On the same screen, you will also get the option to sort in increasing order or the reverse one. You are free to choose an order of your choice or select both, alternatively aiming for better readability. 

Step 6: Click on ‘Sort’ for the changes to take effect. And your dataset is sorted by date. 

Processed Data

Method 2: Using the formula 

Step1: Once your data is ready, select all the cells on which you want to apply to sort. For example, you picked to sort cells A1 to A10. 

Step 2: Once the selection is done, click on a nearby cell to use the formula and specify the required parameters. Let’s say, you clicked on C1. 

Step 3: Use this formula in your selected cell, C1 in this example, to input this formula: 

SORT(range, sort_column, is_ascending, [sort_column2,is_ascending2....])

SORT(A2:B10, 1, TRUE). 

Formula to sort data in Google Sheet

Step 4: Press enter and your data is sorted in a new column C. 

Common issues with dates in Google sheets 

While working with sheets is quite easy, several issues have been encountered in the past. Here’s a brief summary of the common ones:

1. Google sheets show the date as #VALUE! Error. 

While this is a valid error thrown by the tool, it also has a valid reason behind it. So, a date column is expecting a combination of date, month and year. In case you’ve provided any value that does not fit with the required data, Google sheets will not be able to read it. 

There can be various invalid inputs, such as you might have entered a special character or a function by or pure text where the cell is expecting none of these. And you can face this error value instead. 

2. Correct data is provided, but Google sheet is not reading the date 

If you have provided all the 3 fields correctly then it is most likely that you have used an incorrect indentation. By default, Google sheets keep the dates right aligned and text aligned to the left. While this may not seem much of a concern, these configurations are valued the most by such advanced tools. 

3. Date vs DateValue which function to use in Google sheets 

Since we have already discussed the date function, let’s learn about the DATEVALUE. This function is actually the interpreter for Google sheets to read and process the date. Data similar to 29th March 2022 is appealing to the human eye, but for this tool, it is merely a set of numbers. 

Google sheet utilizes the DATEVALUE method to understand these dates and apply your request, such as sorting them.

Leave a reply:

Your email address will not be published.

Site Footer