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

This example demonstrates a formula that works in older Excel versions. It is a larger formula and I recommend Excel 365 subscribers to use the formula in section 1 that is specifically for Excel 365.

The formula is an array formula and must be entered in a particular way. Here is how:

1. Copy the formula below.
2. Select the destination cell.
3. Paste the formula to the destination cell, there are two ways:
   1. Shortcut: Press and CTRL key and then press v once. Release CTRL key.
   2. Use the ribbon: Go to tab "Home" on the ribbon. Press with left mouse button on button "Paste".
      
   3. Use the mouse: Press with right mouse button on on the destination cell. A popup menu appears. Press with left mouse button on the "Paste" button on the popup menu.
4. Press and hold CTRL and SHIFT keys at the same time.
5. Press Enter once.
6. Release all keys.

The formula has a leading and trailing curly bracket. Don't enter these characters yourself, they appear automatically.

Array formula in cell E7:

You now need to copy the cell to adjacent cells as far as needed. Here is how:

The animated image above demonstrates how t:

• Copy the formula using the dot on the lower right corner of the cell.
• change the formatting from date to currency using the "Format painter" on the ribbon.

You can also copy/paste using these steps:

1. Select the cell containing the formula.
2. Copy the cell containing the formula, you have two options:
   1. Shortcut: Press and hold CTRL key. Press c once. Release CTRL key.
   2. Use the ribbon: Go to tab "Home" on the ribbon. Press with left mouse button on button "Copy".
   3. Use mouse: Press with right mouse button on on the cell containing the formula. A popup menu appears. Press with left mouse button on the "Copy" button on the popup menu.
      
3. Paste to the destination cells.

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 array formula demonstrated in section 2 above shows an Excel error when no more values can be displayed. The formula returns a #NUM! error that can be hidden using the IFERROR function.

The formula above shows how to combine the IFERROR function and the formula. It returns a blank cell instead of the #NUM! error.

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 the 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.

Convert a range to an Excel table

1. Select cell range A2:B38.
2. Go to tab "Insert" on the ribbon.
3. Press with left mouse button on "Table" button. A popup menu appears. Enable the check box if the data set has column headers.
4. Press with left mouse button on the OK button.

The data set is now an Excel Table which has a lot of benefits:

1. Sort data.
2. Filter data.
3. Change Table styles.
4. Create banded rows and/or columns.
5. Create a total.

Filter dates in an Excel Table

1. Press with left mouse button on the "black arrow" near header name Date.
2. Hover over "Date filters" with the mouse pointer. A popup menu appears.
3. Press with left mouse button on "Between...". See the image below.
   
4. Select the start date and the end date.
   
5. Press with left mouse button on OK button to apply changes.

The Excel Table instantly shows the filtered data. The blue row numbers tells you that the Excel Table is filtered as well as the icon on the button next to the column name has changed.

Back to top

4. Filter rows in an Excel table using VBA

In this example, you can type a start date in cell B1 and an end date in cell B2. Press the button named "Filter" and the Excel Table is instantly filtered. The button is a "Form Control" button that is assigned to a VBA macro.

What is a Form Control?
Form Controls are built-in user interface elements in Microsoft Excel and other Microsoft Office applications that allow users to interact with spreadsheets or documents. These controls include buttons, checkboxes, dropdown lists, scroll bars, and more. Form Controls are relatively simple and are linked directly to spreadsheet cells, allowing for easy data manipulation without the need for complex coding.

• Simple to use and configure.
• Work with Excel’s built-in macros and formulas.
• Do not require programming knowledge (VBA is optional).
• Faster and more lightweight compared to ActiveX controls.
• Limited customization options. 

What is VBA?

VBA (Visual Basic for Applications) is a programming language developed by Microsoft that is used for automating tasks in Microsoft Office applications such as Excel, Word, and Access. It allows users to create macros, automate repetitive tasks, and enhance Office functionality with custom scripts.

What is a macro?

In Microsoft Excel, a macro is a sequence of instructions written in Visual Basic for Applications (VBA) that allows you to automate repetitive tasks time consuming tedious tasks. Macros can perform a wide range of actions from simple formatting changes to complex data manipulations.

How to get this to work?

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 (Form Control)

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)