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

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.

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.

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

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.

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

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.

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

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

Finally, the results look like the image given below.

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.

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

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

Similar Readings

• How to Calculate Average If within Date Range in Excel
• How to Find Max Date in Range with Criteria in Excel

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.

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

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

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

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.

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

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

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

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

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

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.

• Second, go to the Insert tab >> select 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

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

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

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.

Download Practice Workbook

Conclusion

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.

<< Go Back to Date Range | Date-Time in Excel | Learn Excel