First of all, let me explain what I mean by filtering the current week horizontally in Google Sheets. Imagine you have a dataset with several columns, and the column headers represent dates. The first row of your dataset contains the header row with dates. Now, you want to filter only the columns that have dates falling within the current week. That’s what I’m referring to as filtering the current week horizontally in Google Sheets.
Suitable Functions
To achieve this, we have two functions at our disposal that have the filtering ability – Query and Filter. Additionally, we may also need to use the Weeknum function to find the week number of the current week. However, since the Weeknum function is not part of Query, we’ll need to use a workaround when filtering data using this function.
But don’t worry, the Filter function supports horizontal filtering and the Weeknum function. So, we can directly use Weeknum-based criteria within the Filter function. In this article, I’ll first demonstrate how to filter dates falling within the current week in the header row using the Filter function. Then, we can move on to using the Query function.
Filter Function to Filter the Current Week Horizontally
There are two steps involved in horizontally filtering the current week in Google Sheets:
- Filter the columns that contain dates falling within the current week in the header row.
- Combine the filtered result with the text column (the first column).
To filter the current week horizontally, we can use the following Filter formula:
=filter(B2:AF,weeknum(B2:AF2)=weeknum(today()))Insert the above formula in cell AH2 to filter the dataset horizontally based on the Weeknum criteria. The Filter function syntax for our formula is:
FILTER(range, condition1)Where:
- Range (the range to filter): B2:AF
- Condition: weeknum(B2:AF2)=weeknum(today())
Finally, combine the first text column with the filtered result by editing the formula as follows:
={A2:A,filter(B2:AF,weeknum(B2:AF2)=weeknum(today()))}This way, we can use the Filter function to filter the data falling within the current week horizontally in Google Sheets.
Query Current Week’s Data Horizontally
Usually, we use the Query function to filter data vertically by specifying the columns to filter based on the values in those columns. However, when it comes to filtering the current week horizontally using Query, we encounter a small challenge – there is no Weeknum scalar function in Query.
To filter the current week horizontally using the Query function, we’ll use a workaround method. Here’s how:
Logic
In the SELECT clause of the Query function, we can specify the columns to filter. For example:
=query({A2:AF},"Select Col1, Col2")Based on the Filter formula result mentioned earlier, we need to filter Column 1 and columns 21 to 27. Cell U2 (the 21st column) contains the start date of the current week, and cell AA2 (27th column) contains the end date of the current week.
To query the current week horizontally, we need to first find the column numbers dynamically using the following steps:
-
Use the MATCH formula to return the starting column number of the current week. Insert the following formula in cell AH1:
=ArrayFormula(match(weeknum(today()),weeknum(A2:AF2),0)) -
Use the MATCH formula again to return the ending column number of the current week. Insert the following formula in cell AI1:
=ArrayFormula(match(weeknum(today()),weeknum(A2:AF2),1))
Both MATCH formulas are the same, except for the last argument.
Now, let me explain these formulas to you.
The syntax of the MATCH function is as follows:
MATCH(search_key, range, search_type)In the formula, the last argument (search_type) can be 0 or 1. When it’s 0, the formula treats the range as unsorted and returns the exact match of the value (Weeknum) found at the starting of the range. If it’s 1, the formula treats the range as a sorted range and returns the largest value less than or equal to the search_key (Weeknum).
It’s important to note that the first row in the dataset containing the dates must be sorted in ascending order for the Match formulas to work correctly.
Now, let’s move on to the Query clause to filter the current week horizontally:
=query({A2:AF},"Select Col1, Col21, Col22, Col23, Col24, Col25, Col26, Col27",1)To make the formula dynamic, we can use the previously mentioned Match formulas like this:
Steps to Follow:
- Write a Sequence formula to replace “Col21, Col22, Col23, Col24, Col25, Col26, Col27” in the above formula:
=sequence(AI1-AH1+1,1,AH1)
The SEQUENCE syntax is as follows:
SEQUENCE(rows, columns, start)- Rows (number of rows to return): AI1-AH1+1 (which means match_formula_2 – match_formula_1 + 1)
- Columns (number of columns to return): 1
- Start (sequence starting from): AH1 (match_formula_1)
To skip using the helper cells AH1 and AI1, we can rewrite the formula as follows:
=sequence(ArrayFormula(match(weeknum(today()),weeknum(A2:AF2),1))-ArrayFormula(match(weeknum(today()),weeknum(A2:AF2),0))+1, 1, ArrayFormula(match(weeknum(today()),weeknum(A2:AF2),0)))We should further modify the formula to make it the Query clause text “Col21, Col22, Col23, Col24, Col25, Col26, Col27”:
=textjoin(",",true,ArrayFormula("Col"&sequence(ArrayFormula(match(weeknum(today()),weeknum(A2:AF2),1))-ArrayFormula(match(weeknum(today()),weeknum(A2:AF2),0))+1,1,ArrayFormula(match(weeknum(today()),weeknum(A2:AF2),0))))))To simplify the formula, we can shorten it to the following and call it dynamic_select_formula:
=ArrayFormula(textjoin(",",true,"Col"&sequence(match(weeknum(today()),weeknum(A2:AF2),1)-match(weeknum(today()),weeknum(A2:AF2),0)+1,1,match(weeknum(today()),weeknum(A2:AF2),0))))Finally, here’s the non-dynamic Query formula to filter the current week’s data horizontally in Google Sheets:
=query({A2:AF},"Select Col1, Col21, Col22, Col23, Col24, Col25, Col26, Col27",1)Just delete the formula part “Col21, Col22, Col23, Col24, Col25, Col26, Col27”,1). So the formula should look like this:
=query({A2:AF},"Select Col1,"&Then, paste the dynamic_select_formula at the end of the formula:
=query({A2:AF},"Select Col1,"&ArrayFormula(textjoin(",",true,"Col"&sequence(match(weeknum(today()),weeknum(A2:AF2),1)-match(weeknum(today()),weeknum(A2:AF2),0)+1,1,match(weeknum(today()),weeknum(A2:AF2),0)))))That’s it! These are the two methods to filter the current week horizontally in Google Sheets.
Enjoy!
Click here to know more about Google Sheets and SEO at Crawlan.com
