FILTER data between two dates

In this worksheet, we have sample order data that contains a date field.

Let’s set up the FILTER function to extract data between two dates.

Whenever you’re working with dates in Excel, you’re working with numbers.

Now, in H5, I’ll enter the FILTER function.

Arrayis the full set of data.

Forinclude, to begin with, let’s filter on dates greater than March 15, 2020.

Now, the safest way to input a date in an Excel formula is to use theDATE function.

This makes it impossible for Excel to misunderstand the date.

The year is 2020, the month is 3, and the day is 15.

When I press enter we see only orders after March 15, 2020.

To begin, I’ll enclose the existing expression in parentheses, then copy it to the clipboard.

As we’ve already seen, for AND logic, we use multiplication.

I’ll paste in the expression, and adjust to match dates less than May 15.

When I enter the formula, FILTER returns orders after March 15 and before May 15.

Now in many cases it makes sense to expose FILTER criteria directly on the worksheet.

This makes it a lot easier to enter the start and end date.

To use these dates, I just need to replace the DATE functions in theincludeargument with cell references.

Now I can easily change either date and get a new set of filtered data.

Finally, notice we aren’t matching any orders that fall on the start or end date.

Now the FILTER function will include these dates.