To filter rows that contain dates of previous n dates in Google Sheets, we can use the function Query. Previous n dates can be last 1 day, 2 days, 3 days etc. This tutorial is based on the current date based Query filtering in Google Sheets.
Actually, I do have a similar tutorial on this site that I have posted in the recent past – Find the Past or Future Closest Date to Today in Google Sheets. But that approach is different. Here I am using the function Query to filter last n dates.
How to Filter Previous N Dates in Google Sheets Using Query
Without a sample data, we can’t proceed further. So here is that demo data and the expected output. Here the ‘n’ is considered as 1. The formula that I am going to provide you is flexible enough. So you don’t face any issue with changing the ‘n’ in the formula.
As per my example, today’s date is 04/Dec/2018 (my system clock date). That means I want to filter the rows in the range that contain the dates closest to 04/Dec/2018.
Example:
The Query Formula to Filter Last N Days in Google Doc Sheets:
=query({A2:C},"Select * where Col1<date '"&text(today(),"yyyy-mm-dd")&"' order by Col1 Desc Limit 1")
This formula would return only one row (previous 1 day). To return previous/last two dates change the 1 in the last part of the formula to 2, I mean change the clause Limit 1 to Limit 2.
Formula Logic: How The Query Filters Previous N Dates in Google Sheets
The formula filters all the rows in the range if the dates in column 1 are less than today’s date.
Note: Unlike the Filter function, you should use the date criteria in Query carefully. For the time being, you can follow my above formula to use the today() function in Query.
If you want to know more about the use of dates as the criterion in Query, do check my guide about converting a date to a string using the Long-winded Approach or the Query date criteria guide.
Now back to the formula logic. As mentioned, I have used the today() function in Query to filter previous n dates in Google Sheets. Then sorted that output within the same Query in descending order.
Why the sorting in descending order?
After sorting the filtered output in descending order, the dates closest to today() will be on the top. Now use the Limit clause in Query to limit the number of rows to ‘n’.
More Resources:
1. How to Use Query With Importrange in Google Sheets.
2. Combine Similar Rows and Sum Values in Google Sheets.
3. How to Sum, Avg, Count, Max, and Min in Google Sheets Query.
4. How to Use Arithmetic Operators in Query in Google Sheets.
