Không thể sort theo timestamp trong google sheet
Google Sheets has really easy tools to use when it comes to sorting data. Show
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 FunctionThe 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. Here’s how to organize a Google Sheet by date for the above data: =SORT(A2:A9,1,TRUE) The above SORT function takes three arguments:
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: The below formula will sort this dataset based on the date column. =SORT(A2:B9,1,TRUE) 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. Related Reading: Sort by Color in Google Sheets Sort by Date Using the SORT Range FunctionalityIf 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. Here’s how to organize by date in Google Sheets:
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 Filterssometimes 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. 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:
You can also sort by multiple columns if you wish. Common Issue When Sorting by Date in Google SheetsThe 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 DateBy 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. Check using DATEVALUE functionIf 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. 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 SheetsHere’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 QuestionsWhy 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 DateNow that you know how to sort by date in Google Sheets You may also like the following tutorials:
|