Excel Filter for Date Range 2 Ways

In Excel, you can use the drop down arrows in the table headings, to sort or filter the data. In this example, we’ll filter for dates that fall within a specific date range. The video shows two ways to accomplish that task.

Use a Date Filter

The first way to filter for a date range, is to use one of the Date Filter options. There are dynamic date range options, such as This Week, or Last Year.

For this example, I’ll use one of the static date range options – Between. Unfortunately, you can’t click on a cell, to use its contents as the date, but there’s a drop down list. You can select an item from that drop down, or type the dates in the boxes.

Use Conditional Formatting

The second option that I used in the video was a Filter by Font Color. Thanks to UniMord for suggesting this technique!

First, I created a temporary worksheet formula, using the AND function. It compared the date in cell B4 to the start date, and to the end date.

=AND(B4>=$G$2,B4<=$H$2)

Once the worksheet formula was working correctly, I copied it, using Ctrl+C.

Add Conditional Formatting

Next, I selected the Date column in the table, and added conditional formatting to all the date cells.

For the Formula, I used Ctrl+V to paste in the worksheet formula that I had copied in the previous step.

For the formatting, on the Font tab, I selected Black – Text 1 – 5% lighter.

Excel will detect the difference, but on the worksheet, the dates should all look the same. That way, if you want to print the sheet, you won’t need to worry about the font colour.

Filter by Colour

Now, with the conditional formatting applied, click the drop down arrow in the Date column heading.

Click on Filter by Color, then click the black color under Filter by Font Color.

The table is filtered to show only the rows in the selected date range.

And it’s easy to change the date range. Just type new dates on the worksheet, then apply the Filter by Color again.

NOTE: You can clear the cells where the worksheet formulas were entered. They were just used for testing.

Get the Workbook

To download the AutoFilter for Date Range workbook, go to the AutoFilter page on my Contextures website. The zipped file is in xlsx format, and does not contain macros.

And you can find more Conditional Formatting examples on my Contextures site too!

Video: Filter for Date Range

Here’s the video that shows the steps for two ways to filter for a date range in Excel. Use a Date filter, or use Filter by Color, after applying Conditional Formatting. The timeline is below the video

Video Timeline:

0:00 Intro
0:17 Use the Date Filter
2:01 Another Way to Filter
2:42 Create a Formula
4:03 Add Conditional Formatting
6:21 Get the Workbook

__________________