To filter data and extract records between two dates, you can use a formula based on the FILTER function with Boolean logic.
How to filter data between two dates?
Here are the steps to filter data between two dates in Excel:
- Select cell F6.
- Type =FILTER(data_range, (date_range >= start_date) * (date_range <= end_date)).
- Press Enter.
- The formula filters and displays data between the specified start and end dates.
Example
The following example shows a dataset in the range B3:D12 containing product names, dates, and quantities. We want to filter the data to include only rows where the date falls within a specified range, defined in cells F3 (start date) and G3 (end date). The formula in cell F6 uses the FILTER function to extract rows meeting the criteria where the date is greater than or equal to the start date and less than or equal to the end date. If no rows match the criteria, the formula outputs “Not found.”
Formula:
=FILTER(B3:D12,(C3:C12>=F3)*(C3:C12<=G3),”Not found”)
The filtered results are displayed in columns F to H.
Explanation
Evaluate the formula:
Condition: The expression (C3:C12 >= F3) * (C3:C12 <= G3) creates a logical test. The first part of the formula (C3:C12 >= F3) checks if each date in the range C3:C12 is greater than or equal to the date in F3. It returns an array of boolean values: TRUE if the condition is met and FALSE if not. The second part of the formula (C3:C12 <= G3) checks if each date in C3:C12 is less than or equal to the date in G3. It also returns an array of TRUE or FALSE values.
The * sign between these two boolean expressions is an AND logical operator. This multiplication produces an array of 1s and 0s, where 1 represents a row that meets both conditions (falls within the date range), and 0 represents a row that does not.
The FILTER function will include rows from B3:D12 that meet the above condition. If both conditions are TRUE for a row, that row is included in the results.
If no rows meet the criteria, the function returns “Not found,” as specified in the third argument of FILTER.
Why the FILTER function is preferred? The FILTER function avoids the need for complex array formulas with INDEX and SMALL, which can sometimes misbehave in certain contexts. Since FILTER returns the exact rows that meet your conditions, it’s simpler and more efficient.