This tutorial elaborates on how to filter data by date range using the Filter menu, QUERY function, or FILTER function in Google Sheets.

If you use the Filter menu filtering feature, you can filter the source data, whereas the formulas (QUERY and FILTER) will return the filtered data in a new range. Choose the one based on your preference.

Filtering Data by Date Range Using the Filter Menu

We have data in columns A and B, and column A contains dates. Let’s apply a filter to the date column first.

1. Select column A.
2. Click Data > Create a filter.

In the top row, in two blank cells, enter the start and end dates to filter the date column. For example, enter the start date in cell C1 and the end date in cell D1.

Now, let’s proceed to the steps to filter this data by the given date range.

Steps:

1. Click on the filter drop-down in cell A1 and click “Filter by condition”.
2. From the drop-down below, select “Is between”.
3. Enter =$C$1 in the first field and =$D$1 in the second field. The cell references must be static, which is why the $ sign is included with the criteria references.
4. Click “OK”.

When you want to filter by a different date range, enter those dates in C1 and D1. Then, simply click the filter drop-down in cell A1 and click “OK”.

These two actions are required each time you change the filter criterion in cells C1 and D1, which makes this approach non-dynamic.

Note: If you want to filter out the dates that fall within the given date range, in step 2 above, select “Is not between” instead of “Is between”.

Filtering Data by Date Range Using Formulas

If you prefer using formulas, I suggest you enter them in a new sheet within the source data file.

You won’t be able to edit the formula results directly. Any changes you want to make should be made to the source data, and they will be immediately reflected in the formula output.

We can use either the FILTER or QUERY functions to filter data by date range in Google Sheets.

Out of these two, the FILTER function is easier to apply. So let’s start with that.

FILTER Function Approach

Assume our sample data is in A1:B in Sheet1 where A1:B1 contains the field labels. In another empty sheet in that file, enter the date range in A1:B1.

Then enter this formula in cell A3:

=FILTER(Sheet1!A2:B, ISBETWEEN(Sheet1!A2:A, A1, B1))

This follows the syntax FILTER(range, condition1, [condition2, …]).

Where:

• range: Sheet1!A2:B (the source data range excluding the header row).
• condition: ISBETWEEN(Sheet1!A2:A, A1, B1) (it returns TRUE wherever the dates in Sheet1!A2:A fall within the range A1 and B1).

The FILTER function filters the rows wherever the condition is TRUE.

Note: If you want to filter out the dates that fall within the given date range, simply wrap the ISBETWEEN function with NOT, which turns FALSE to TRUE and TRUE to FALSE.

=FILTER(Sheet1!A2:B, NOT(ISBETWEEN(Sheet1!A2:A, A1, B1)))

Now, we will see how to filter date ranges using the QUERY function in Google Sheets.

QUERY Function Approach

One advantage of QUERY is that it can include the header row in the formula. It recognizes the header row and ensures it is not filtered out.

However, unlike FILTER, it may be a little more challenging to remember the correct use of date criteria in QUERY.

=QUERY(Sheet1!A1:B, "SELECT A, B WHERE A>=DATE '"&TEXT(A1,"yyyy-mm-dd")&"'  AND  A<=DATE '"&TEXT(B1,"yyyy-mm-dd")&"' ", 1) 

This QUERY formula filters the data in A1:B in Sheet1 by the given date range in cells A1 and B1 in the formula-entered sheet.

It follows the syntax QUERY(data, query, [headers]).

Where:

• data: Sheet1!A1:B
• query: "SELECT A, B WHERE A>=DATE '"&TEXT(A1,"yyyy-mm-dd")&"' AND A<=DATE '"&TEXT(B1,"yyyy-mm-dd")&"' ": filters the dates in column A between the specified dates in cells A1 and B1.
• headers: 1 (the number of header rows in the data)

Note: To filter the data for the date range that does not fall within the dates specified in cells A1 and B1, use the following QUERY:

=QUERY(Sheet1!A1:B, "SELECT A, B WHERE A<DATE '"&TEXT(A1,"yyyy-mm-dd")&"'  OR  A>DATE '"&TEXT(B1,"yyyy-mm-dd")&"' ", 1) 

Resources

• Analyze Transactions by Date Range in Sheets: Start & End Dates
• Find the Date or Date Range from Week Number in Google Sheets
• COUNTIF to Count by Month in a Date Range in Google Sheets
• How to Include a Date Range in SUMIFS in Google Sheets
• How to Vlookup a Date Range in Google Sheets
• Array Formula to Conditionally Sum Date Ranges in Google Sheets
• AVERAGEIFS ArrayFormula Using MMULT in Google Sheets (Date Range)
• Count Unique Dates in a Date Range – 5 Formula Options in Google Sheets
• Conditionally Lookup Dates in Date Range in Google Sheets (Array Formula)
• Consecutive Dates to Date Ranges in Google Sheets: The REDUCE Method