Apply Conditional Formatting Based on a Date

With Conditional Formatting in Excel, you can use dates to create a rule for highlighting cells. There are some pre-built rules that you can use to highlight the cells quickly. And there is also a way to create a custom rule based on a date. In this tutorial, we learn to use both ways in detail.

Steps to use Dates for Conditional Formatting

1. Select the data where you have dates.
2. Go to Home > Conditional Formatting > Highlight Cells Rules.
3. From there, click on the option “A date occurring”.
4. In the dialog box, select the rule you want to use.
5. In the end, click OK to apply.

And the moment you click OK, it highlights all the cells where you have the date occurring in the current month. With the same dialog box, you have more options to use with the dates.

Create a Custom Rule Based on Dates

There could be situations when you need to apply a conditional formatting rule, but that rule is not there. Well, you can create a custom rule with a formula. Go to Home > Conditional Formatting > New Rule.

Highlight Dates Older than 30 Days, 60 Days, and 90 Days

1. First, select the data, open the New Rule dialog box, and click “Use a formula to determine which cell to format”.
   • 30 Days Older: =$A1<=(TODAY()-30)
   • 60 Days Older: =$A1<=(TODAY()-60)
   • 90 Days Older: =$A1<=(TODAY()-90)
2. After that, to specify the formatting click on the “Format” button.
3. Next, specify the formatting, cell color, font color, or bold or italic.
4. In the end, click OK.

You can see in the above example that we have used formula to highlight dates older than 30 days from the current date.

Highlight Weekdays and Weekend Dates

And using a formula (Weekday), you can highlight dates that are weekends. You can see in the example below the formula we have used highlights two dates.

=WEEKDAY($A1,2)>5

And if you want to highlight weekdays, use the formula below.

=WEEKDAY($A1,2)<5

Using Range of Dates in Excel

In the same way, if you want to highlight cells based on a date range, you can use the function AND to create a formula with two conditions.

=AND(A1>=(TODAY()-45),A1<=(TODAY()-30))

In the above example, we have created a range of 15 days. 30 days from the current date and 45 days from the current date.

Using a Date from Another Cell

Now, if you want to use the date in a cell, you need to refer to a cell where you have that date. See the example below; we have specified cell C1, where we have the date.

=IF($C$1<>"",$A1>=$C$1,FALSE)

In this way, you can create a dynamic rule, and changing the date in the cell will also change the highlighted cells.