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.

the Filter Command to Filter by Date

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.

the Filter Command to Filter by Date

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

the Filter Command to Filter by Date

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.

the Filter Command to Filter by Date

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

the Filter Command to Filter by Date

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.

the Filter Command to Filter by Date

There is only one month available- July.

the Filter Command to Filter by Date

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.

the Filter Command to Filter by Date

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.

the Filter Command to Filter by Date

Here’s our filtered output for the day-

the Filter Command to Filter by Date

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.

the Filter Command to Filter by Date

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

the Filter Command to Filter by Date

It’s the filtered data for this current year.

the Filter Command to Filter by Date

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

The filtered data of Last Year

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.

the Filter Command to Filter by Date

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.

the Filter Command to Filter by Date

Select is after or equal to from the list.

the Filter Command to Filter by Date

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.

the Filter Command to Filter by Date

Select 4/10/2017.

the Filter Command to Filter by Date

Then click on And option.

Use Filter Command

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

Use Filter Command

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.


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.

Conditional Formatting to Filter by Date in Excel

Steps:

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

Conditional Formatting to Filter by Date in Excel

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-

=AND(D5>=$C$15,D5<=$D$15)

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

Conditional Formatting to Filter by Date in Excel

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.

Conditional Formatting to Filter by Date in Excel

At this moment, just click OK.

Now our filtered data is highlighted with the picked color.


Similar Readings


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

=FILTER(E5:E12,MONTH(D5:D12)=7,"Empty")

Hit the Enter button to get the filtered sales.

FILTER & MONTH Functions to Apply Date-filter in Excel

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

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

Press the Enter button to get the filtered sales.

FILTER, MONTH & YEAR Functions in Excel to Filter By Date

⏬ 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 Data in Excel using Formula


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

Clearing a Filter from Data

Now see that the filter is removed.

Clearing a Filter from Data

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 

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo