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.


Download Practice Book

Download the practice book from here.


5 Easy Ways to Use Custom Date Filter in Excel

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.

Use Filter Command to Apply Custom Date Filter in Excel

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

Use Filter Command to Apply Custom Date Filter in Excel

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

Use Filter Command to Apply Custom Date Filter in Excel

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

Use Filter Command to Apply Custom Date Filter in Excel

Here, we want to filter the sales amount from 1/1/2021 to 12/31/2021. You can change this section according to your needs.

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

Use Filter Command to Apply Custom Date Filter in Excel

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

Use Filter Command to Apply Custom Date Filter in Excel

  • Finally, you will see the below result.

Use Filter Command to Apply Custom Date Filter in Excel


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.

Apply Custom Date Filter Using Excel Conditional Formatting

  • Secondly, select Cell F5 and type the formula:
=AND(C5>=$C$14,C5<=$D$14)
  • Hit Enter to see the result.

Apply Custom Date Filter Using Excel Conditional Formatting

In this formula, we have used the AND function. This formula checks if the value of Cell C5 is between 1/1/2021 and 12/31/2021. If it is between 1/1/2021 and 12/31/2021, then, it will show TRUE. Otherwise, it will display FALSE.

Apply Custom Date Filter Using Excel Conditional Formatting

  • You can see the results in the picture below.

Apply Custom Date Filter Using Excel Conditional Formatting

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

Apply Custom Date Filter Using Excel Conditional Formatting

  • In the following step, select Column C.

Apply Custom Date Filter Using Excel Conditional Formatting

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

Apply Custom Date Filter Using Excel Conditional Formatting

  • In the Formatting Rule Window, select ‘Use a formula to determine which cells to format’.
  • Then, type the formula:
=AND(C5>=$C$14,C5<=$D$14)
  • After that, select Format.

Apply Custom Date Filter Using Excel Conditional Formatting

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

Apply Custom Date Filter Using Excel Conditional Formatting

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

Apply Custom Date Filter Using Excel Conditional Formatting

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

Apply Custom Date Filter Using Excel Conditional Formatting


Similar Readings


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.

Use Custom Date Filter with Excel Pivot Table

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

Use Custom Date Filter with Excel Pivot Table

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

Use Custom Date Filter with Excel Pivot Table

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

Use Custom Date Filter with Excel Pivot Table

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

Use Custom Date Filter with Excel Pivot Table

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

Use Custom Date Filter with Excel Pivot Table

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

Use Custom Date Filter with Excel Pivot Table

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

Use Custom Date Filter with Excel Pivot Table

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


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.

Insert Formula to Use Custom Date Filter in Excel

Let’s follow the steps below to learn more.

STEPS:

  • To begin with, select Cell D14.
  • After that, type the formula below:
=FILTER(D5:D11,MONTH(C5:C11)=8,"Empty")
  • Finally, press Enter to see the results.

In this formula,

  • The first argument (D5:D11) is the array where it looks for the Sales Amount.
  • The second argument MONTH(C5:C11)=8 denotes that the month is August.
  • If it finds no value of August, then it prints Empty.

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


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


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. Visit the ExcelDemy website for more articles like this. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.


Related Articles

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo