For example, if you have dates in the range B4:G11, and want to highlight dates that have the same month and year as the date June 1, 2015, select the range B4:G11 and create a new CF rule that uses this formula: Note: it’s important that CF formulas be entered relative to the “active cell” in the selection, which is assumed to be B4 in this case. Once you save the rule, you’ll see all dates that occur in June 2015 highlighted.
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: 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
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
