This tutorial on Sheets elaborates on how to filter by date range using filter menu in Google Sheets. That means narrow down a dataset based on date criteria.

Using the filter command, you can conditionally narrow down a dataset in Google Sheets. To access the filter command, click on the menu Data > Create a filter.

To filter a date column by date range using the filter menu in Sheets, there are two options. What are they?

1. Filter using built-in rule.
2. Filter using custom formula.

You can either use the built-in rule “Is between” or use a custom formula in the filter command.

To filter by date range using the filter menu command, you need at least two criteria. They are the start date and end date.

Filter by Date Range Using Built-In Rule in Sheets

I have the following sample data to filter based on “Receipt Date” which is in column F.

You can see the required two criteria to filter a date range in cell H1 and J1. Keep the criteria in the first row. Otherwise, it may be got hidden while filtering.

Filter Is Between

I want to filter the “Receipt Date” column to only display the items received between 01/07/2019 and 02/07/2019 (both the dates inclusive).

Steps:

1. Select the range A1:F and go to Data > Create a filter.
2. Click on the filter drop-down in cell F1 and click “Filter by condition”.
3. Then From the drop-down below, select “Is between”.
4. Enter =$H$1 in the first field and =$J$1 in the second field. The cell references must be static. That’s why I have included the $ sign with the criteria references.
5. Click “OK”.

This way you can filter a dataset by date range using the built-in rule in Google Sheets.

Filter Formula Equivalent to the Filter Is Between:

To filter by date range and then extract the data to a new range, use the FILTER function instead of the filter menu.

In a new range, for example, in cell G1 you can use the below FILTER formula for this.

=filter(A2:F18,F2:F>=H1,F2:F<=J1)

Filter Is Not Between

This is another method to narrow down a dataset based on date criteria. This is just reverse to the earlier example.

If you select “Is not between” instead of “Is between” in point # 3 in the above steps, the filter will hide the rows containing the dates in column F between 01/07/2019 and 02/07/2019.

Filter Formula Equivalent to the Filter Is Not Between:

Here is the filter formula which is equivalent to the “Is not between” menu command. Similar to earlier formula, you can use it in cell G1 to experiment.

=filter(A2:F18,(F2:F<H1)+(F2:F>J1))

Filter by Date Range Using Custom Formula in Sheets

Instead of using “Is between” you can use the below custom filter menu formula.

=(F2:F>=$H$1)*(F2:F<=$J$1)=1

What about “Is no between”?

Use this one.

=(F2:F<>$H$1)*(F2:F<>$J$1)=1

How to apply these custom formulas in the menu command?

Steps:

1. Select A2:F and go to Filter > Create a filter.
2. Click “Filter by condition”
3. Select “Custom formula is”
4. Enter either of the above custom formulas.

That’s all about filter by date range using filter menu in Google Sheets.