In Google Sheets,
To filter by month, we have a table with several columns, one of which is a date. We've added filters to the table, and we want to filter by month, similar to what can be done in Excel.
Unfortunately, instead of a list of months - we get a continuous list of dates as appears in the picture.
There are many options to reach the desired result, among them is to click on Clear, to clear the selection of dates, then write in the search window the month and year we want: let's say 10/2023 and then click on Select all and finally OK.
Good option but a bit awkward ...
The proposed option is relatively simple and includes one formula with two functions FILTER and DATE .
In the example below, I've done the filtering on the same sheet for visual convenience, but of course you can do the same thing on another sheet.
In cell K18 I wrote the number 11. The cell will represent the number of the month, in the example November.
And in cell K20 I wrote the following formula:
=filter(A20:I79,month(A20:A79)=K18)
The formula says to filter the entire range A20:I79, provided that the month in column A20:A79 is equal to the number in cell K18.
Now every time we type a number in cell K18 the whole table will be filtered according to the relevant month.
If we want to filter both by month and by year - we can work according to the same logic and write the formula:
=filter(A20:I79,month(A20:A79)=K18,year(A20:A79)=L18)
In cell L18 I added a year to the selection.
The FILTER function works the same, this time with two conditions.
Successfully!
Comments