Explanation

The DATE function creates a proper Excel date with given year, month, and day values. Then, it’s simply a matter of comparing each date in the range with the date created with DATE. The reference B4 is fully relative, so will update as the rule is applied to each cell in the range, and any dates greater than 8/1/2015 will be highlighted.

Greater than or equal to, etc.

Of course, you can use all of the standard operators in this formula to adjust behavior as needed. For example, to highlight all dates greater than or equal to 8/1/2015, use:

=B4>=DATE(2015,8,1)

Use another cell for input

There is no need to hard-code the date into the rule. To make a more flexible, interactive rule, use another cell like a variable in the formula. For example, if you want to use cell C2 as an input cell, name cell C2 “input”, enter a date, and use this formula:

=B4>input

Then change the date in cell C2 to anything you like and the conditional formatting rule will respond instantly.

Explanation

This formula uses the TEXT function to concatenate the month and year of each date. Then, the two dates are tested for equality. TEXT is a useful function that allows you to convert a number to text in the text format of your choice. In this case the format is the custom date format “myyyy”, which translates to: month number without leading zeros & 4-digit year. For example, if A1 contains the date 9-Jun-2015, TEXT(A1,“myyyy”) will produce the text string “62016”.

Use other cells for input

You don’t need to hard-code a date into the rule. To make a more flexible rule, you can use another cells like a variable. For example, if you name cell E2 “date”, you can rewrite the formula like so:

=TEXT(B4,"myyyy")=TEXT(date,"myyyy")

Then whenever you change the date in E2, the conditional formatting rule will update instantly. This simplifies the formula and makes it easier to read.

Other formulas

There are several other formulas you could use to solve this problem. For a run-down of 5 different options, see: 5 formulas to highlight dates by month and year