Here get the essential formulas and ideas to filter data based on a date range. To filter a date range, you can either use the Filter function or Query. Here let’s use the Filter function to filter data based on this week, last week, last 30 days, last 60 days and so on.
Other than Filter, I’ll use some of the Date functions in the criteria part of the Filter. If you are new to Google Sheets Filter as well as Date functions, you can learn that essential Google Sheets functions by following the below two links.
- How to Use the Filter Function In Google Sheets [Basic and Advanced Use].
- How to Utilise Google Sheets Date Functions [Complete Guide].
I am going to apply my filter formulas in a two-column dataset with dates in the first column and some other values in the second column. You can include multiple columns in the Filter.
Filter This Week in Google Sheets
(Dynamic Formula to Filter a Dataset for the Current Week)
For your info, today’s date is Fri, 04-Oct-2019. So the dates that fall in this week are from Sun, 29-Sep-2019 to Sat, 05-Oct-2019.
Your current date (today’s date) may be different and this week’s date range may also be different. But the formula that I am sharing with you is adaptive to any current day and ‘this week’. If your dataset range is the same as per my example, then you can use my formula as it’s.
Formula to Filter This Weeks Date Range in Google Sheets:
=filter(A2:B18,Weeknum(A2:A18)=weeknum(today()))
Formula Criteria Part Explanation
You may know that each week in a year has a unique number called week number associated with it. Using the Weeknum function we can return the current week’s week number.
Filter Rage: A2:B18
Filter Criterion: Weeknum(A2:A18)=weeknum(today())
The filter matches the week number of the dates in A2:A18 with today’s week number. If matches it returns the matching rows.
Filter Last Week in Google Sheets
(Dynamic Formula to Filter a Dataset for the Previous Week)
To filter last week’s data, first, you must find the week number of the last week. How to do that?
The logic here is finding any dates in the last week from today’s date. Once found, we can use that Date within the Weeknum function to return the week number of the previous week.
I guess you need some explanation, right? Here you go!
How to Dynamically Find the Week Number of Last Week?
To find the week number of last/previous week, follow the below steps.
=WEEKDAY(today())The above Weekday formula returns the day of the week (a number between 1 to 7) number of today’s date. Then deduct that number (number of days) from today’s date.
=today()-WEEKDAY(today())This formula will return the date of last Saturday. That means the last day in the last week. We just want any day that falls in the last week. We have now one date.
Now it’s easy to get the week number of the last week in Google Sheets. Here is the formula for that.
=weeknum(today()-WEEKDAY(today()))To filter last week’s data you can use the just above formula as the criterion in Filter.
=filter(A2:B18,Weeknum(A2:A18)=weeknum(today()-WEEKDAY(today())))
Filter Last 30 Days Data
(Filter a Dataset for the last 30 Days Data)
To filter the data for the last 30 days, use this formula.
=filter(A2:B,A2:A>=today()-30,A2:A<today())I hope the criteria in this filter are self-explanatory. That’s the case with filtering a data set for the last 60 days, 90 days and so on.
Filter Last 60 Days Data
(Filter a Dataset for the last 60 Days Data)
=filter(A2:B,A2:A>=today()-60,A2:A<today())That’s all. Enjoy!
