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

In this article, we will learn to use a custom date filter in Excel. Using a custom date filter one can filter a data series from a date to another specific date. Today, we will demonstrate 5 easy methods. After reading the article, you can easily use the custom date filter. So, without further ado, let’s start the discussion.

How to Use Custom Date Filter in Excel: 5 Easy Ways

To explain the methods, we will use a dataset that contains information about the Sales Amount on different dates for some salesmen. We will use the custom date filter in the dataset and show the sales amount for specific dates. The same dataset will be used throughout the whole article.

1. Use Filter Command to Apply Custom Date Filter in Excel

In the first method, we will use the filter command to apply the custom date filter in Excel. This process is very handy and easy to implement. So, let’s follow the steps below to learn more about the method.

STEPS:

• First of all, select a cell in your dataset.
• Secondly, go to the Data tab and select Filter.

• Instantly, a drop-down arrow will appear in the headers of the dataset.

• After that, click on the drop-down arrow to open the Context Menu.
• From the Context Menu, select Date Filters and then, select Custom Filter.

• After selecting Custom Filter, the Custom AutoFilter window will appear.
• In the Custom AutoFilter window, select ‘is after’ and type ‘1/1/2021’ in the first field.
• In the second field, select ‘is before’ and type ‘12/31/2021’.

• After typing the values in the Custom AutoFilter window, click OK to see results like the picture below.

• Similarly, if you want to filter the sales amount after 1/1/2021, you can select ‘is after’ and type ‘1/1/2021’ in the first field only.
• Click OK to proceed.

• Finally, you will see the below result.

2. Apply Custom Date Filter Using Excel Conditional Formatting

In Microsoft Excel, you can also use Conditional Formatting to apply a custom date filter. This process is quite similar to the previous one but we will use a formula this time. So, let’s follow the steps now to see how we can apply conditional formatting to use a custom date filter in Excel.

STEPS:

• In the first place, you need to type the Starting Date and Ending Date in the dataset.
• In our case, we want to filter the values from 1/1/2021 to 12/31/2021. That is why we have type 1/1/2021 and 12/31/2021 in Cell C14 and D14 respectively.

• Secondly, select Cell F5 and type the formula:

• Hit Enter to see the result.

• Thirdly, drag the Fill Handle down.

• You can see the results in the picture below.

• Now, select Cell C4 and press Ctrl + Shift + L together to apply Filter in the dataset.
• You can also do this from the Data tab like Method-1.

• In the following step, select Column C.

• After selecting Column C, go to the Home tab and select Conditional Formatting. A drop-down menu will occur.
• Select New Rule from there. It will open the New Formatting Rule window.

• In the New Formatting Rule Window, select ‘Use a formula to determine which cells to format’.
• Then, type the formula:

• After that, select Format.

• In the Format Cells window, change the Font Color to Red and click OK to proceed.

• Again, click on the drop-down arrow in the Date column. A drop-down menu will occur.
• Select Filter by Color and then, select the Red color.

• Finally, you will see results like the picture below.

3. Use Custom Date Filter with Excel Pivot Table

In the next method, we will use the Pivot Table feature to apply a custom date filter to our dataset. Pivot Table helps users to display data in a formed manner in Excel. Let’s observe the steps below to see how we can apply this method.

STEPS:

• Firstly, select a cell in the dataset.
• Then, navigate to the Insert tab and select the PivotTable icon.

• A message will pop up.
• Click OK to proceed.

• After clicking OK, a new sheet will occur and it will contain the PivotTable Fields on the right side of the screen.

• Select Salesmen, Date, and Sales Amount in the PivotTable Fields section.

• After that, click on the drop-down arrow of the Column Labels column.

• From the drop-down menu, select Date Filters and then, select Custom Filter. It will open the Date Filter (Date) window.

• In the Date Filter window, select ‘is after’ and type 01/01/2021.
• It denotes that the pivot table will contain values from the date 01/01/2021.

• Lastly, you will see the filtered results like the picture below.

4. Insert Formula to Use Custom Date Filter in Excel

You can also use a formula to use a custom date filter in Excel. We will use the combination of the FILTER function and the MONTH function. The FILTER function extracts a filtered result from a dataset and the MONTH function denotes a month.

In this method, we will use a formula to filter the Sales Amount for the August month. The formula will look for the sales for the August month irrespective of the year. Here, in the dataset, we have added a section for the Month and the Sales amount.

Let’s follow the steps below to learn more.

STEPS:

• To begin with, select Cell D14.
• After that, type the formula below:

• Finally, press Enter to see the results.

5. Use Excel Macro Record to Apply Custom Date Filter

We can also use the ‘Record Macro’ option to apply a custom date filter in Excel. For this purpose, you need to save the workbook in the .xlsm format. Without any delay, let’s jump to the steps.

STEPS:

• First of all, select a cell in the dataset and press Ctrl + Shift + L to apply Filter.

• Secondly, go to the Developer tab and select Record Macro. It will open the Record Macro window.

• In the Record Macro window, type a name for the macro and click OK to proceed.
• We have named it CustomDateFilter.

• Thirdly, click on the drop-down arrow in the Date column. A drop-down menu will occur.
• Select Date Filters and then, select Custom Filter. It will open the Custom AutoFilter window.

• In the Custom AutoFilter window, select ‘is after’ and type ‘1/1/2021’ in the first field.
• In the second field, select ‘is before’ and type ‘12/31/2021’.

• Then, click OK to proceed.

• Once again, navigate to the Developer tab and select Stop Recording.

• Now, open a similar sheet where you have the same dataset.
• Go to the Developer tab and select Macros from there.

• Click on Run in the Macro window.

• Finally, you will see results like the picture below.

Download Practice Book

Download the practice book from here.

Conclusion

In this article, we have demonstrated 5 easy and quick ways to Use Custom Date Filter in Excel. I hope this article will help you to perform your tasks easily. Moreover, we have also discussed the method to apply a custom date filter with the Pivot Table feature. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise.

<< Go Back to Date Filter | Filter in Excel | Learn Excel