Extract records between two dates

This article presents methods for filtering rows in a dataset based on a start and end date.

The image above shows a formula in cells E7:F10 that extracts rows from cell range B3:C17 if the dates fall between the conditions specified in cells F2 and F3.

I will be showing you how to filter dates using these Excel features:

1. Excel 365 dynamic array formula - more powerful Excel functions.
2. Excel formula - earlier Excel versions.
3. Excel Table - easy to use.
4. Excel Table and VBA.
5. Autofilter - easy to use.
6. Advanced filter - handles more complicated criteria.

Dynamic array formulas require Excel 365, Excel Tables require Excel 2007 or a later version. The remaining methods should work on almost any Excel version.

1. Filter rows using array formulas based on a date range - Excel 365

This example shows the new FILTER function in cell E7, it uses the start and end dates to filter rows containing a date that falls between the start and end dates. The result is an array that is returned to cells below and to the right automatically.

Excel 365 dynamic array formula in cell E7:

Dynamic array formulas are a new feature in Excel 365 that allows you to enter a single formula in a cell and get results that automatically spill into other cells, as far as needed. This can save you time and make your formulas more efficient, as you don't need to create separate formulas for each cell or use the more complicated array formulas.

To create a dynamic array formula, you just need to enter the formula in a cell and press Enter. The results will automatically spill into the surrounding cells, as long as those cells are empty. You will get a a #SPILL error if adjacent cells are populated.

The dynamic array formula spills values to cells below or cells to the right depending on the result array size and shape.

Explaining formula

The FILTER function lets you do amazing things that were not possible before, you needed much larger formulas in earlier Excel versions.

Step 1 - Compare dates to the start date

The less than, greater than, and equal signs are logical operators that let you compare values in Excel formulas, in this case, date values. The result is a logical value, in other words, a boolean value TRUE or FALSE.

Date values in Excel are actually stored as whole numbers. January 1, 1900, is the start date and each subsequent day is represented by a higher serial number. For example, January 1, 1900, is represented by the serial number 1, January 2, 1900, is represented by the serial number 2, and so on.

F2<=B3:B17 returns {TRUE; TRUE; TRUE; ... ; TRUE}

Step 2 - Compare dates to the end date

F3>=B3:B17 returns {FALSE; FALSE; FALSE; ... ; TRUE}

Step 3 - Multiply boolean values

Multiplying boolean values results in AND logic being performed, TRUE if all of the conditions are TRUE, and FALSE if any of the conditions are FALSE.

Here are the possible results of multiplying boolean values:

TRUE * TRUE = TRUE
TRUE * FALSE = FALSE
FALSE * TRUE = FALSE
FALSE * FALSE = FALSE

However, keep in mind Excel converts the result to their numerical equivalents:

TRUE - 1
FALSE - 0 (zero)

Also, in this particular example, Excel multiplies values that correspond to its position in the array meaning the first value in the first array is only multiplied by the first value in the second array and so on.

Using parentheses allows you to specify the order in which the operations should be performed. In this case, it is important to compare the values before multiplying them, as not doing so will result in incorrect output.

(F2<=B3:B17)*(F3>=B3:B17) returns {0;0;0;0;0;0;1;1;0;1;0;0;0;0;1}

Step 4 - Filter rows based on logical test

The FILTER function extracts values/rows based on a condition or criteria.

Function syntax: FILTER(array, include, [if_empty])

FILTER(B3:C17,(F2<=B3:B17)*(F3>=B3:B17)) returns {45044,3800; 45022,0; 45056,2300; 45011,9900}

Back to top

2. Filter rows using array formulas based on a date range - earlier Excel versions

Array formula in cell E7:

Back to top

2.1 How to create an array formula

1. Select cell E7.
2. Type the array formula.
3. Press and hold Ctrl + Shift simultaneously.
4. Press Enter once.
5. Release all keys.

The formula has a leading and trailing curly bracket like this:
{=INDEX($A$2:$B$38, SMALL(IF(($A$2:$A$38<=$H$2)*($A$2:$A$38>=$H$1), MATCH(ROW($A$2:$A$38), ROW($A$2:$A$38)), ""), ROW(A1)), COLUMN(A1))}

Don't enter these characters yourself, they appear automatically.

Back to top

2.2 How to copy the array formula

1. Select cell E7.
2. Left-press and hold with the mouse button on the dot in the lower right corner of cell D7.
3. Drag to cell F7, release left mouse button.
4. Press the left mouse button and hold on the dot in the lower right corner of cell F7.
5. Drag down with the mouse as far as needed.
6. Release the left mouse button.

Back to top

How to change F7:F10 cell formatting:

1. Select a cell containing the source formatting, in this example any cell in C3:C17.
2. Double-press with left mouse button on the left mouse button on the "Format Painter" button on the ribbon tab "Home".
3. Select cell range F7:F10.

See the animated image above.

Back to top

2.3 How to remove #num errors

The IFERROR function if the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, the IFERROR function returns the value argument.

Function syntax: IFERROR(value, value_if_error)

2.4 Get Excel *.xls file

filter-records-between-two-dates.xls
(Excel 97-2003 Workbook *.xls)

Back to top

3. Filter rows using an Excel table

This example shows how to filter rows in an Excel Table based on a date range. First, we will convert the data set to an Excel Table. Second, we will learn how to filter the data using a start and end date.

3.1 Convert range to a table

1. Select range A2:B38
2. Go to tab "Insert"
3. Press with left mouse button on "Table"
4. Press with left mouse button on OK!

3.2 Filter dates

1. Press with left mouse button on Black arrow near Date header
2. Hover over "Date filters"
3. Press with left mouse button on "Between..."
   
4. Select dates
   
5. Press with left mouse button on OK!

Back to top

4. Filter rows in an Excel table using VBA

In this example, you can type a date in cell B1 and B2. Press the button and the table is instantly filtered.

Copy the vba code below into a regular module. Create a button and assign the macro. There are more detailed instructions below.

4.1 VBA code

Sub TblFilterDates()

Worksheets("Sheet3").ListObjects("Table13").Range.AutoFilter _
Field:=1, _ 
Criteria1:=">=" & Worksheets("Sheet3").Range("B1") _
, Operator:=xlAnd, _
Criteria2:="<=" & Worksheets("Sheet3").Range("B2")
End Sub

This VBA macro filters a table named "Table13" on a worksheet called "Sheet3" based on dates specified in cells B1 and B2, in the first column (Field:=1) of the table. The filter criteria are :

• Criteria1: ">=" & Worksheets("Sheet3").Range("B1")
  This specifies that the dates in the first column of the table should be greater than or equal to the date in cell B1 on the "Sheet3" worksheet.
• Criteria2: "<=" & Worksheets("Sheet3").Range("B2")
  This specifies that the dates in the first column of the table should be less than or equal to the date in cell B2 on the "Sheet3" worksheet.
• Operator:=xlAnd
  This specifies that the filter should use "AND" logic to combine the two criteria meaning both criteria must match.

The AutoFilter method is used to apply a filter to a range of cells or a table in a worksheet. When a filter is applied, only the rows that meet the specified criteria are displayed, and the remaining rows are hidden. The AutoFilter method can be used to filter data based on a specific value, a range of values, or a pattern.

Back to top

4.2 Where to copy vba code?

1. Press Alt+F11 to open the Visual Basic Editor (VBE).
2. Press with mouse on Insert.
3. Press with mouse on "Module" to insert a new module to you workbook.
4. Copy and paste code into code window, see the image above.

Back to top

4.3 How to create a button

1. Press with left mouse button on "Developer" tab on the ribbon.
2. Press with left mouse button on "Insert" button.
3. Create a button (form control) on the worksheet.
4. Press with right mouse button on on the button.
   
   A popup menu appears. Press with left mouse button on "Assign Macro...".
5. A dialog box shows up.
   
6. Select the macro name.
7. Press with left mouse button on OK button.

Back to top

Back to top

Filter records category

Search for a text string in a data set and return multiple records
This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]

Match two criteria and return multiple records
This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]

Extract all rows from a range that meet criteria in one column
Lookup with criteria and return records.

Excel categories

Comments (9)