# How to filter a sheet by a specific date?

I have a sheet with multiple date columns determining the lifecycle of a product.

How do I filter all the columns, so I can see on which Month the lifecycles of products are at?

e.g. I filter by month of "July", I can see the product of "X" has got a Testing date falling July

So in the screenshot, typing in "May" should show products A, B and E because they have checkpoints that occur in May (First Review and Second Review)

Note: needs to be the month of the calendar year, so an edge case is we don't want to see months of previous or future years?

We tried using an advanced filter with the MONTH function but that didn't work.

Any suggestions?

## 2 Answers

Name the cell you want to type the filter month into filter_month.

You can then use this formula in a new column in your table:

``````=OR(TEXT(B2:E2,"mmmm")=filter_month)
``````

If you drag it down, this will create a column of TRUE or FALSE. You can filter on TRUE to get the rows you want.

This works because the TEXT function used with a range here will create an array of four items - each of the months, full-length, of each of the columns B through E. Then, comparing each of those to filter_month will create an array of four items, containing TRUE or FALSE, depending on whether the month from that column matches the text typed into filter_month. Then, the OR function will just say, if any single one of the four items is TRUE, then return TRUE.

An array (CSE) formula wrapped with `IFERROR` solves the issue.

• An array (CSE) Formula in cell `I91`:

``````{=IFERROR(INDEX(A\$91:A\$95,SMALL(IF(MONTH(\$B\$91:\$C\$95)=5,ROW(\$A\$91:\$A\$95)-MIN(ROW(\$A\$91:\$A\$95))+1),ROWS(\$H\$98:H98))),"")}
``````

N.B.

• `SMALL(IF(MONTH(\$B\$91:\$C\$95)=5`, here `5` is month's number for `May`, and is editable also.

• You may replace `5` with cell reference in the formula, then it will be like for example, `SMALL(IF(MONTH(\$B\$91:\$C\$95)=\$G\$90`, here `G90` has month's number.

• Finish formula with Ctrl+Shift+Enter and fill across.

• Adjust cell references in the formula as needed.

• @tomaytotomato,, yes it's possible,,, if U find that my post is the solution you were looking for then you may accept it as an Answer as well Up vote too. ☺ May 26 '20 at 5:07