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

how to filter date range in excel


Download Practice Workbook


5 Ways to Filter Date Range in Excel

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

how to filter date range in excel

  • Then unmark January and March and click OK.

You will see the information about the sales in February.

how to filter date range in excel

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

how to filter date range in excel using filter command

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

how to filter date range in excel

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

Read More: How to Calculate Date Range in Excel


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

how to filter date range in excel

  • 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)

how to filter date range in excel

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

Read More: How to Use Custom Date Filter in Excel (5 Easy Ways)


2. Filtering Date by Using FILTER Function

Using the Excel FILTER function would be a smart idea to Filter 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.

how to filter date range in excel using filter function

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

  • Type the following formula in cell F5.
=FILTER(B5:D14,MONTH(B5:B14)=2,"No data")

how to filter date range in excel

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.

Read More: Excel VBA: Filter Date before Today (With Quick Steps)


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

how to filter date range in excel using pivot table

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

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

how to filter date range in excel using pivot table

  • 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).

how to filter date range in excel using pivot table

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.

how to filter date range in excel using pivot table

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.

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

Read More: How to Filter Date Range in Pivot Table with Excel VBA


Similar Readings


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.

how to filter date range in excel using VBA

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

how to filter date range in excel using VBA

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 start date and end date.

  • Now, run Macros from the Excel sheet.

how to filter date range in excel using VBA

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

how to filter date range in excel using VBA

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

Read More: Excel VBA: Filter Date Range Based on Cell Value (Macro and UserForm)


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.
=AND(TODAY()-B5>=60,TODAY()-B5<=80)

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.

how to filter date range in excel

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

how to filter date range in excel

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

how to filter date range in excel

Thus you can filter the date range in Microsoft Excel.

Read More: Excel Formula to Add Date Range (11 Quick Methods)


Practice Workbook

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.


Conclusion

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


Related Articles

Nahian

Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

2 Comments
  1. That was wonderful! I didn’t expect anybody to be able to help, but your method 5 was perfect for my needs. Thank you!

Leave a reply

ExcelDemy
Logo