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. Before we begin, I want to remind you that Excel dates are just large serial numbers, as you can see if I temporarily apply general number formatting. Whenever you’re working with dates in Excel, you’re working with numbers. Now, in H5, I’ll enter the FILTER function. Array is the full set of data. For include, to begin with, let’s filter on dates greater than March 15, 2020. I start with the full set of dates in range F5:F20, then enter the greater-than operator (>). Now, the safest way to input a date in an Excel formula is to use the DATE 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 extend this logic to include an end date, I need to use two logical expressions, one for start date, and one for the end date. 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. If I inspect the include argument with the F9 key, you can see the boolean array used to filter the data. 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 the include argument 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. If we want to include these, we need to use greater than or equal to, and less than or equal to. Now the FILTER function will include these dates.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
