Filter Data for Certain Number of Weeks in Google Sheets Using Formula

0
100
Filter Data for Certain Number of Weeks in Google Sheets

Do you know how to filter data for certain number of weeks in Google Sheets like 1 week, 2 weeks, 3 weeks etc.? If not, this tutorial is for you. I am going to provide you a very flexible formula for this purpose. You can modify and use this custom filter formula for your purpose.

In Google Doc Spreadsheet, there are two options to filter data using number of weeks as condition. They are QUERY and FILTER.

In this tutorial I’m going to use FILTER function as it’s very simple compared to QUERY, in terms of handling the date criteria. Using date criteria in Query is tough to follow.

How to Filter Data for Certain Number of Weeks in Google Sheets

This time I’m not posting a screenshot of my sample data as it’s too long. It’s like this.

Cell A1 contains the date 01/10/2017 and progressively to Cell A31 where the date is 31/10/2017. Corresponding column range B1:B31 contain some random numbers. You can put any data in this range as it’s not important or going to affect our tutorial.

That’s all. It’s a two column data where we are using our above filter tricks.

We are going to filter last two weeks data from the above data range and I’ve considered the weeks are starting from Monday to Sunday.

Formula to Filter Data for Certain Number of Weeks

Scenario # 1

Our starting date as per sample data is 01/10/2017 and end date is 31/10/2017. If you consider Monday to Sunday as week start and week end dates, the perfect filtered data would be with in the below date ranges.

Formula # 1 to Filter Data for Certain Number of Weeks

Formula:

=filter(A1:B,A1:A>=(max(A1:A)(weekday(max(A1:A),2))+1)2*7,A1:A<=(max(A1:A)(WEEKDAY(max(A1:A)))+1))

When you use this formula to filter data for certain number of weeks in Google Sheets note the following points.

  1. When you copy and paste the above formula from this post to your sheet, please rewrite all the double quotes above.
  2. Here the data range is A1:B. You can change it as per your data range like A1:E or A1:F etc.
  3. The above filter formula will filter the data for last two weeks. If you want to change the number of weeks, see the red coloured part in the formula. When you change 2*7 to 5*7 it considers as 5 weeks and accordingly the filter would happen.
  4. The same is applicable to Scenario # 2 and Scenario #3 below. How to develop this filter formula then? I know you are curious about it. I will tell you that part at the end of this post.

Scenario # 2

Here in this scenario, I’ve considered last two weeks as below. Here the filtering we are going to apply from the most recent date in our data, i.e., 31/10/2017 to two weeks back.

Formula # 2 to Filter Data for Certain Number of Weeks

Formula:

=filter(A1:B,A1:A>=(max(A1:A)-(weekday(max(A1:A),2))+1)-2*7,A1:A<=(max(A1:A)))

Here, every points which I’ve mentioned under Scenario # 1 is applicable when you use this formula on your sheet.

Scenario # 3

Here you can guess what is the so called two weeks. Its’ the most recent half week + past one week.

Formula # 3 to Filter Data for Certain Number of Weeks

Formula:

=filter(A1:B,A1:A>=(max(A1:A)-(weekday(max(A1:A),2))+1)-1*7,A1:A<=(max(A1:A)))

This is the same formula as Scenario # 2. But only one difference is here. In Scenario # 1 and Scenario # 2, the Red coloured part in the formula are 2*7. Here it’s 1*7. That means when you want to filter 5 weeks data, you should put 4*7 in this last formula. But in the first and second formula it’s 5*7.

Conclusion:

If you know the FILTER formula usage in Google Sheets, then you may only want to know one thing with the above formulas. That is, how to find last certain number of weeks. That part, I’ve detailed in a separate post Here. Hope you find this Filter Data for Certain Number of Weeks in Google Sheets tutorial useful. Enjoy!

LEAVE A REPLY

Please enter your comment!
Please enter your name here