Are you struggling to filter data in Google Sheets based on a specific date range? Look no further! In this article, I will share essential formulas and ideas that will help you filter data effortlessly. Whether you need to filter data for this week, last week, last 30 days, or even last 60 days, I’ve got you covered.
Using the Filter Function or Query
When it comes to filtering a date range in Google Sheets, you have two options: the Filter function or Query. In this article, I will focus on using the Filter function. If you’re new to Google Sheets and want to familiarize yourself with the Filter function and Date functions, check out the following resources:
- How to Use the Filter Function In Google Sheets [Basic and Advanced Use]
- How to Utilize Google Sheets Date Functions [Complete Guide]
Let’s Get Started!
To demonstrate the filtering process, let’s consider a two-column dataset. The first column contains dates, and the second column contains corresponding values. Remember, you can include multiple columns in the Filter function.
Filter This Week in Google Sheets
Let’s start by filtering data for the current week. Please note that today’s date is Fri, 04-Oct-2019. Therefore, the dates falling within this week are from Sun, 29-Sep-2019 to Sat, 05-Oct-2019.
To filter this week’s date range, use the following formula:
=filter(A2:B18,Weeknum(A2:A18)=weeknum(today()))
Formula Criteria Part Explanation
Each week in a year has a unique number, known as the week number. We can use the Weeknum function to return the current week’s week number.
- Filter Range: 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 there’s a match, it returns the corresponding rows.
Filter Last Week in Google Sheets
Now, let’s filter data for the previous week. To do this, we need to find the week number of the last week.
Here’s how you can dynamically find the week number of the last week:
- Use the WEEKDAY formula to determine the day of the week number for today’s date.
=WEEKDAY(today()) - Subtract the above number of days from today’s date.
=today()-WEEKDAY(today())This formula returns the date of the last Saturday, which is the last day in the previous week.
With the date from the last week, we can now calculate the week number using the formula:
=weeknum(today()-WEEKDAY(today()))To filter last week’s data, use the formula below as the criterion in the Filter function:
=filter(A2:B18,Weeknum(A2:A18)=weeknum(today()-WEEKDAY(today())))
Filter Last 30 Days Data
If you want to filter data for the last 30 days, use this formula:
=filter(A2:B,A2:A>=today()-30,A2:A<today())The criteria in this filter are self-explanatory. You can apply a similar approach to filter data for the last 60 days, 90 days, and so on.
Filter Last 60 Days Data
To filter a dataset for the last 60 days, use the following formula:
=filter(A2:B,A2:A>=today()-60,A2:A<today())That’s all there is to it! Now you can confidently filter data in Google Sheets based on different date ranges. Happy filtering!
Crawlan.com – Your go-to source for all things Google Sheets!
