How to Filter Date Range in Excel (5 Easy Methods)

This article will provide some valuable methods on how to filter a range of date(s) in Excel. Suppose you have sales information for one month, but you don’t want to know the sales that happened each day of that month. Rather, you need to know what happened on some specific days or in a specific week. For that purpose, you should filter out a range of date so that you can easily find out what was the condition of the business in that period of time.

Here we will be working on the following dataset. It shows the sales quantity of some electronic products in a shop on some different dates in the months of January, February and March.

1. Using Excel Filter Command to Filter Date Range

The easiest operation to filter out a range of dates is using the Filter command from the Editing ribbon. Let’s see how we can do this.

1.1. Filtering Date Range by Selection

Suppose we want to know about the sales quantity in the months of January and March. So we need to Filter out the dates in the month of February.

Steps:

• Select any cells among B4 and D4 and then go to Home >> Sort & Filter >> Filter.

• After that, click on the marked icon in cell B4 (Shown in the following picture).

• Then unmark January and March and click OK.

You will see the information about the sales in February.

• To get the information about January and March, select the range B10:D12 and do the right click on any of the selected cells.
• Then click on Delete Row.

• A warning message will appear. Just click OK.

• This operation will terminate all the information of the product sales in February. Now select Filter from Sort & Filter ribbon again.

Now you will see the information about the sales in January and March only.

Thus you can Filter a range of dates in Excel to see your desired information.

1.2. Filtering Date Range Using Date Filters

We are eager to know about the sales quantity in the months of January and March. So we need to Filter out the dates in the month of February.

Steps:

• Select any cells among B4 and D4 and then go to Home >> Sort & Filter >> Filter

• After that, click on the marked icon in cell B4 (Shown in the following picture).

• Select Custom Filter from Date Filter (Shown in the next figure).

Remember, you want to see the sales information in the months of January and March. So you have to filter out February month. So to do this,

• Set the Date as ‘is before 01-02-22 or is after 07-02-22’ (see in the figure below)

• Now click OK and you will see the sales information in the months of January and March.

Thus you can Filter the date range as you wish. You may also find other options in Date Filter like Today, Yesterday, Next Month etc. If you want to filter date ranges in a different way, you may use those options.

2. Filtering Date by Using FILTER Function

Using the Excel FILTER function would be a smart idea to Filter the date range. Imagine you want to know about the information of sales in February. Let’s see what procedure you should follow regarding this method.

Steps:

• First, make a new chart like the following figure.

• Make sure that the number format of column F is set to Date.

• Type the following formula in cell F5.

The MONTH function helps the FILTER function to return the information of sales based on the month we put in the formula. Here we want to see the sales information in February, so we are checking if the date range B5:B14 belong to month number 2. If yes, we will see the sales history of February month. Else, we get no data.

• Now press ENTER and you will see all the information about product sales in February.

Thus you can Filter the date range at a glance.

3. Utilizing Pivot Table to Filter the Range of Dates

In this section, I’ll show you how to filter a date range with the help of Pivot Table. Let’s say you want to know about the total sales in January. Just follow the procedure given below.

Steps:

• First, select the range B4:D12. Then go to Insert >> Pivot Table

• A dialog box will show up. Just click OK.

You will see PivotTable Fields on the right side in a new Excel Sheet. It has all Fields from the Column Headings of your dataset. There are four Areas Filters, Columns, Rows, and Values. You can drag any Field on these Areas.

• Now click on Date in the PivotTable Field. You will see another field Month will automatically appear.

• Now, this is a tricky part. You need to unmark Date but mark Products and Sales Qty. from the field
• Then drag the Months field from the area of Rows to Filters (Shown in the following image).

This operation will show every Sales and Products of the dataset in a Pivot Table.

• To see the Sales in January, click on the arrow of the marked area in the following picture and then select Jan.
• After that, just click OK.

Now you will be able to see all the Products and their respective Sales in the Pivot Table. You can also see the total sales of January month.

In this way, you can easily filter the date range using a Pivot Table. In this case, we filtered out the dates of February and March.

4. Applying VBA to Filter Date Range

We can filter the date range via VBA too. Suppose you just want to know about the sales in February and March. Let’s discuss the process below.

Steps:

• First, open Visual Basic from the Developer Tab.

Then, it will open a new window of Microsoft Visual Basic for Applications.

• Now, open Insert >> select Module.

• Type the following code in the VBA Module.

Public Sub DateRangeFilter()
    Dim StartDate As Long, EndDate As Long
    StartDate = Range("B10").Value
    EndDate = Range("B14").Value
    Range("B4:B14").AutoFilter field:=1, _
        Criteria1:=">=" & StartDate, _
        Operator:=xlAnd, _
        Criteria2:="<=" & EndDate
End Sub

Here, I created a Sub DateRangeFilter, where I declared two variables StartDate and EndDate as Long.

As we want to know about the sales in the months of February and March, we set the first date of February as our start date (cell B10) and the last date of March as our end date (cell B14) using the Range and Value method. Then we used the AutoFilter method to Filter this date range from B4:B14 by setting criteria for the start date and end date.

• Now, run Macros from the Excel sheet.

• After that, you will only see the dates of February and March.

Thus you can filter the date range using a simple VBA code.

Read More: Excel VBA: Filter Date Range Based on Cell Value

5. Using Excel AND and TODAY Functions to Filter Date Range

Suppose you want to know about the sales history with dates between 60 and 80 days ago from today. You can follow this approach.

Steps:

• Make a new column, name it as you wish, In this case, I’ll name it Filtered Date.
• Then type the following formula in cell E5.

Here, the TODAY function identifies the dates between 60 and 80 days ago from today. Then we use this logic for the AND function. Then the AND function returns the values according to logic, 

• Hit the ENTER key and you will see the output in cell E5.

• Now use the Fill Handle to AutoFill lower cells.

• Select cell E5 and then select Home >> Sort & Filter >> Filter

• Now Click on the marked arrow, unmark FALSE, and then click OK (shown in the following figure).

• After executing this operation, you will see the sales history among your desired range of dates.

Thus you can filter the date range in Microsoft Excel.

Read More: [Fixed!] Excel Date Filter Is Not Grouping by Month

Practice Section

Here I am giving you the dataset on which I applied this method. I hope this might be helpful for you to practice these methods on your own.

Download Practice Workbook

Conclusion

This article emphasizes how to filter date range in Excel. We have applied pretty simple methods here. Filtering date ranges can be very useful when you work with a huge dataset and you need to know about some incidents or events or information within a specific period. I hope you may benefit from this article. If you have easier methods in your mind or any feedback, please feel free to leave them in the comment box.

Knowledge Hub

<< Go Back to Filter in Excel | Learn Excel