How to Pull Data from a Date Range in Excel (7 Handy Ways)

Get FREE Advanced Excel Exercises with Solutions!

Unquestionably, Microsoft Excel excels at crunching numbers! Now, this means you can perform tedious calculations, like filter date ranges, in the blink of an eye. In this regard, Excel has become a convenient and valuable tool. Keeping this in mind, this article demonstrates 7 useful ways how to pull data from a date range in Excel.

How to Pull Data from a Date Range in Excel: 7 Ways

To begin with, let’s suppose the Sales Data in the B4:D13 cells contain the Item, Date, and Sales columns respectively.  Here, we want to demonstrate how to pull data from a date range in Excel using various features, tools, and functions of Excel. Henceforth, let’s see each method step-by-step.

Dataset for how to pull data from a date range in excel

Here, we have used the Microsoft Excel 365 version; you may use any other version at your convenience.

1. Using FILTER Function

Now, our first method needs no introduction since the title befittingly describes its action 😀. Here, we use the FILTER and MONTH functions to extract the required dates from the dataset.

📌 Steps:

  • First and foremost, go to the B17 cell >> enter the formula given below.

=FILTER(B5:D13,MONTH(C5:C13)=4,"No Info.")

Formula Breakdown
  • FILTER(B5:D13,MONTH(C5:C13)=4,”No Info.”) → filter a range or array. Here, B5:D13 is the array argument, while MONTH(C5:C13)=4 is the include argument that selects the values corresponding to the month of “April”. Lastly, “No Info.” is the optional if_empty argument that is returned by the function if there are no matches.

📃 Note: The FILTER function is available on Microsoft Excel 365, if you’re using an older version of Excel, then please check the second method.

how to pull data from a date range in excel using FILTER Function

Read More: How to Use IF Formula for Date Range in Excel

2. Combining INDEX, MATCH, SMALL, IF, ROW, and COLUMN Functions

Alternatively, we can combine the IFERROR, INDEX, MATCH, SMALL, IF, ROW, and COLUMN functions to pull data from a date range in Excel. Better still, this method is compatible with older versions of Excel.

📌 Steps:

  • First of all, move to the B17 cell >> copy and paste the expression shown below.

=IFERROR(INDEX($B$5:$D$13, SMALL(IF(($C$5:$C$13<=$G$5)*($C$5:$C$13>=$G$4), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), ""), ROW(A1)), COLUMN(A1)), "No Info.")

Formula Breakdown
  • COLUMN(A1) → returns the column number of a cell reference.
    • Output → 1
  • ROW(A1) → returns the serial number of a reference.
    • Output → 1
  • MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)) → returns the relative position of an item in an array matching the given value. Here, ROW($B$5:$B$13) is the lookup_value argument that refers to the “Item” column. Following, ROW($B$5:$B$13) represents the lookup_array argument from where the value is matched.
    • Output → {1;2;3;4;5;6;7;8;9}
  • IF(($C$5:$C$13<=$G$5)*($C$5:$C$13>=$G$4), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), “”) → checks whether a condition is met and returns one value if TRUE and another value if. Here, ($C$5:$C$13<=$G$5)*($C$5:$C$13>=$G$4) is the logical_test argument which prompts the IF function to return the value from MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)) (value_if_true argument) otherwise it returns blank “”  (value_if_false argument).
    • Output → {1;2;””;””;5;6;7;””;9}
  • SMALL(IF(($C$5:$C$13<=$G$5)*($C$5:$C$13>=$G$4),MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), “”), ROW(A1))becomes
    •  SMALL({1;2;””;””;5;6;7;””;9}, 1) returns the kth smallest value in data set.
    • Output → {1}
  • INDEX($B$5:$D$13, SMALL(IF(($C$5:$C$13<=$G$5)*($C$5:$C$13>=$G$4), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), “”), ROW(A1)), COLUMN(A1))becomes
    • INDEX($B$5:$D$13, 1, 1)  → returns a value at the intersection of a row and column in a given range. In this expression, the $B$5:$D$13 is the array argument which is the “Item” column. Next, 1 is the row_num argument that indicates the row location, while 1 is the column_num argument that indicates the column location.
    • Output →”Playstation”
  • IFERROR(INDEX($B$5:$D$13, SMALL(IF(($C$5:$C$13<=$G$5)*($C$5:$C$13>=$G$4), MATCH(ROW($B$5:$B$13), ROW($B$5:$B$13)), “”), ROW(A1)), COLUMN(A1)), “No Info.”)becomes
    • IFERROR(“Playstation”, “No Info.”)  → returns value_if_error if the expression has an error and the value of the expression itself otherwise. Here, “Playstation” is the value argument, and “No Info.” is the value_if_error argument.
    • Output → “Playstation”

Combining INDEX, MATCH, SMALL, IF, ROW, and COLUMN Functions

  • Afterward, observe the steps in real-time in the GIF below to copy the formula into the cell below.

how to pull data from a date range in excel combining INDEX, MATCH, SMALL, IF, ROW, and COLUMN Functions

Read More: VLOOKUP Date Range and Return Value in Excel

3. Employing Date Filter Feature

Besides, if using complex formulas doesn’t suit you then, the following methods can help you out. For instance, we‘ll employ the Date Filter option of Excel to extract the necessary dates.

📌 Steps:

  • In the first place, navigate to the B16 cell >> click the Sort & Filter drop-down >> choose Filter.

Employing Date Filter Feature

  • Next, click the Down-arrow button >> jump to Date Filters >> select Custom Filter.

Using Custom Filter

  • Later, select the “Date is after 1/1/2022 and is before 3/31/2022” criteria >> hit OK.

Entering dates in Custom Autofilter

Finally, the results look like the image given below.

how to pull data from a date range in excel employing Date Filter Feature

4. Utilizing Excel Table

For one thing, we can utilize the Excel Table option to pull data from a date range. Hence let’s see it in action.

📌 Steps:

  • Initially, select the B16:D25 array >> press the CTRL + T shortcut keys to insert Table.

Utilizing Excel Table

  • Not long after, press the Down-arrow button >> choose the months, in this case, “January”, “February”, and “March” >> click on OK.

Filtering dates

Lastly, the final output should look like the picture shown below.

how to pull data from a date range in excel utilizing Excel Table

Similar Readings

5. Implementing PivotTable

Conversely, Excel’s PivotTable feature allows us to sort and group data easily and effectively, so it’s no surprise, that we can apply PivotTable to filter a date range.

📌 Steps:

  • At the very beginning, move to the B4 cell >> click on Insert >> press the PivotTable button >> select the New Worksheet option >> hit OK.

Implementing PivotTable

  • Following this, drag the “Item”, “Date”, and “Sales” Fields into the “Rows”, “Values”, and “Filter” Areas respectively.

Dragging PivotTable Fields

  • At this point, press the Down-arrow button >> tick the Select Multiple Items box >> choose the dates according to your preference >> click on OK.

Selecting Dates

Consequently, the end results should resemble the figure shown below.

how to pull data from a date range in excel implementing PivotTable

6. Using PowerQuery

In addition, the PowerQuery editor is another under-appreciated feature of Excel that can import, transform, and filter data from spreadsheets. So, let’s put this powerful tool to the test.

📌 Steps:

  • To begin with, go to the B5 cell >> press CTRL + T keys to convert the dataset into a Table.

Using PowerQuery

  • Now, move to the Data tab >> choose From Table/Range option.

Selecting to Table or Range option

  • Afterward, follow the steps in the GIF for a live demonstration to filter the dated range.

Filtering specific dates in PowerQuery

  • Then, press the Close & Load drop-down >> choose the Close & Load To option.

Selecting Close & Load to option

  • Furthermore, check the Existing worksheet button >> enter the B16 cell reference >> press OK.

Insertig Table into existing worksheet

Subsequently, the final output appears in the image given below.

how to pull data from a date range in excel using PowerQuery

7. Applying VBA Code

Last but not least, we can also apply VBA Code to pull data from a date range in Excel. Now, it’s simple and easy, so just follow along.

📌 Steps:

  • First, navigate to the Developer tab >> click the Visual Basic button.

Applying VBA Code

  • Second, go to the Insert tab >> select Module.

Inserting Module

For ease of reference, copy the code from here and paste it into the window as shown below.

Sub Extract_date_from_range()
    Dim Begin_date As Long, Finish_date As Long
    Begin_date = Range("G4").Value
    Finish_date = Range("G5").Value
    Range("C16:C25").AutoFilter field:=1, _
        Criteria1:=">=" & Begin_date, _
        Operator:=xlAnd, _
        Criteria2:="<=" & Finish_date
End Sub

VBA Code how to pull data from a date range in excel

Code Breakdown

Here, we’ll explain the VBA code used to extract data from a date range.

  • In the first portion, the sub-routine is given a name, here it is Extract_date_from_range().
  • Next, define the variables Begin_date and Finish_date; assign the Long data type.
  • Then, use the Range.Value property to set the cell references for the starting and ending dates, in this case, “1/1/2022” and “3/31/2022”.
  • In the second portion, apply the Range.AutoFilter method to filter the dates in between the starting and ending dates.

VBA code explanation

  • Third, click the Run button to execute the macro.

Running macro

Eventually, the results should look like the screenshot given below.

how to pull data from a date range in excel applying VBA Code

Read More: How to Use Formula for Past Due Date in Excel

Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

Practice Section

Download Practice Workbook


To sum up, we hope this tutorial has provided you with helpful knowledge on how to pull data from a date range in Excel. Now, we recommend you apply all this know-how in the practice dataset by downloading the practice workbook. In addition, feel free to comment and provide your valuable feedback.

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Eshrak Kader
Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF