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

Get FREE Advanced Excel Exercises with Solutions!

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â€™.

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.

• 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:
`=AND(C5>=\$C\$14,C5<=\$D\$14)`
• Hit Enter to see the result.

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.

• 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:
`=AND(C5>=\$C\$14,C5<=\$D\$14)`
• 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.

Read More: How to Group Dates by Filter 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.

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.

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mursalin Ibne Salehin

Hi there! This is Mursalin. I am currently working as a Team Leader at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I manage and help the writers to develop quality content in Excel and VBA-related topics.

1. Hi! can you filter dates older than a set number of days from today? I tried the is before Today-35 bit that didn’t work

• Hi SEM,
Thanks for your comment. I am replying to you on behalf of ExcelDemy. If you want to filter dates older than a number of days from today, you can follow the steps from Method-2. But, instead of the formula used here, write the following formula.
`=C5<(TODAY()-30)`

Here, I wrote the formula for dates older than 30 days from today. You can change the formula according to your preference.