How to Filter by Date in Excel (4 Quick Methods)

While working in Excel we frequently need to filter data by date for different purposes. There are a lot of ways to do it. This article will provide you with 4 quick methods with sharp steps and vivid illustrations to filter by date in Excel.

Download Practice Workbook

You can download the free Excel template from here and practice on your own.

4 Ways to Filter By Date in Excel

Let’s get introduced to our dataset first. To explore the methods I’ll use the dataset given below which contains some salespersons’ sales in different regions and corresponding dates.

Method 1: Use the Filter Command to Filter by Date

First of all, we’ll use the Filter command in Excel to filter data by date. There are a lot of options in the Filter command which is quite helpful to do that. We can filter in 3 ways-

1.1 Basic Filter

Firstly, We’ll start with some basic filters.

Steps:

Click any data from your dataset.

Then click as follows-

Home > Editing > Sort & Filter > Filter.

Now the Filter option is turned on, and the Filter icon is available to every Headers’ right side. As we’ll filter dates, click the Filter icon of the Date Header.

Then you’ll get many options like the image below-

At this moment if you want to filter data only for the year 2019 then mark on it and unmark all other years.

Later, just click OK.

Now have a look that Excel is only showing the data for the year 2019.

Now if you want to filter by months of 2019 then click the plus sign(+) located to the left side of 2019. The available months of 2019 will open then.

There is only one month available- July.

Now if you want to filter by the specific day of July then again click the plus sign(+) located to the left side of July.

It is showing that there are only two days for the month is available for our dataset, July 15 and July 20.

To show the data for 20th July, just mark on it and unmark other options.

Finally, just press OK.

Here’s our filtered output for the day-

Read More: VBA to Pivot Table Filter Between Two Dates in Excel

1.2 Advanced Filter

Now let’s see some advanced filtering options in Excel Filter.

Steps:

Like the previous part, open the filter options by pressing the Filter icon in the Date header.

Then we’ll get a lot of options for filtering like filter for This Year/ Last Year/ Next Year, This Month/ Last Month/ Next Month,  This Week/ Last Week/ Next Week, Today, Tomorrow, etc.

If I select This Year, it will show only the data of this year.

It’s the filtered data for this current year.

If we select Last Year then it will show us the data of the previous year.

The filtered data of Last Year–

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

1.3 Custom AutoFilter

Instead of using the built-in command, we can give the command manually by using the Custom AutoFilter from the Data Filter options.

Steps:

Click- Data Filters > Custom Filters.

The Custom AutoFilter dialog box will open up.

Now let’s filter the data between the dates 4/10/17 to 8/10/21 using the Custom AutoFilter.

To set the start date, click the drop-down icon in the upper left box in the Date section.

Select is after or equal to from the list.

Now to set the date click the drop-down icon from the upper right box in the Date section. The dates of our dataset will be shown then.

Select 4/10/2017.

Then click on And option.

Later, select is before from the lower-left box.

And end date- 8/10/2021 from the lower right box.

Finally, just press OK.

Then we’ll get all the filtered data between our selected dates.

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

Method 2: Apply Conditional Formatting to Filter by Date in Excel

There are some alternatives to Excel Filter, Conditional Formatting is one of them. In this method, we’ll use it to filter data by date. We’ll filter the data between the dates 4/10/17 to 7/20/2019. I have placed these dates in Cell C15 and C16 consecutively.

Steps:

Select the date column and then click as follows: Home > Conditional Formatting > New Rule.

Now select Use a formula to determine which cells to format from the Select a Rule Type box.

Then type the following formula in Format values where the formula is true box-

Later, click Format, and a formatting dialog box will open up.

Click on Fill and choose a color. I’ve chosen lite green.

Then press OK and it will go back to the previous dialog box.

At this moment, just click OK.

Now our filtered data is highlighted with the picked color.

Similar Readings

• How to Copy and Paste When Filter Is Applied in Excel
• Add Filter in Excel (4 Methods)
• Shortcut for Excel Filter (3 Quick Uses with Examples)
• Excel VBA: Filter Date Range Based on Cell Value (Macro and UserForm)
• How to Filter Last 30 Days of Date in Excel (5 Easy Ways)

Method 3: Combine FILTER & MONTH Functions to Apply Date-filter in Excel

We can use functions to filter data by date in Excel. For that, we’ll use the FILTER and MONTH functions. We’ll filter the sales for the month- of July.

Steps:

Type the following formula in Cell D15–

Hit the Enter button to get the filtered sales.

⏬ Formula Breakdown:

➥ MONTH(D5:D12)=7
The MONTH function will extract the month number from the dates and if it is equal to 7 then it will return TRUE otherwise FALSE–
{FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE}

➥ FILTER(E5:E12,MONTH(D5:D12)=7,”Empty”)
Finally, the FILTER function will return the corresponding data according to the output of the MONTH function that will return as-
{598210}

Read more: How to Filter Cells with Formulas in Excel

Method 4: Join FILTER, MONTH & YEAR Functions in Excel to Filter By Date

There is another combination function that we can use to filter data by date in Excel. They are the FILTER, MONTH & YEAR functions. Here, we’ll filter the sales for February and the Year-2015.

Steps:

In Cell D15 type the following formula-

Press the Enter button to get the filtered sales.

⏬ Formula Breakdown:

➥ YEAR(D5:D12)=2015

The YEAR function will extract the year from the dates and if it gets equal to 2015 then it will return TRUE otherwise FALSE.-

{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}

➥ MONTH(D5:D12)=2

And the MONTH function will extract the month number from the dates and if it is equal to 2 then it will return TRUE otherwise FALSE–

{TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE}

➥ FILTER(E5:E12,(MONTH(D5:D12)=2)*(YEAR(D5:D12)=2015),”Empty”)

Finally, the FILTER function will return the output according to the output of the MONTH and YEAR functions and that will return as-

{442589}

Read More: How to Filter Dates by Month and Year in Excel (4 Easy Methods)

Clearing a Filter from Data

After applying any filter, it’s not difficult to remove the filter. Follow the steps explained below to learn that. Firstly, I’ll show how to remove the filter from any column. Let’s remove the filter from the filtered Date column which we filtered in our first method.

Steps:

Click the Filter icon from the Column Header.

Then just click Clear Filter from “Date”.

Now see that the filter is removed.

Now I’ll show how to remove filters from the whole dataset.

Click as follows-

Home > Editing > Sort & Filter > Filter.

The Filter option is removed from the whole dataset now-

Conclusion

I hope the procedures described above will be good enough to filter by date in Excel. Feel free to ask any question in the comment section and please give me feedback.

Further Readings

• How to Perform Custom Filter in Excel (5 Ways)
• Search Multiple Items in Excel Filter (2 Ways)
• How to Filter Date Range in Pivot Table with Excel VBA
• Filter Multiple Columns Simultaneously in Excel (3 Ways)
• How to Filter Data in Excel using Formula
• Filter Date Range in Excel (5 Easy Methods)