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
Explanation
The AND function takes multiple arguments and returns TRUE only when all arguments return TRUE. The TODAY function returns the current date. Dates in Excel are simply large serial numbers, so you can create a new relative date by adding or subtracting days. TODAY() + 30 creates a new date 30 days in the future, so when a days is greater than today and less than today + 30, both conditions are true, and the AND function returns true, triggering the rule.
Variable days
Of course, you can adjust days to any value you like:
=AND(B4>TODAY(),B4<=(TODAY()+7)) // next 7 days
=AND(B4>TODAY(),B4<=(TODAY()+45)) // next 45 days
Use other cells for input
You don’t need to hard-code the dates into the rule. To make a more flexible rule, you can use other cells like variables in the formula. For example, you can name cell E2 “days” and rewrite the formula like so:
=AND(B4>TODAY(),B4<=(TODAY()+days))
When you change either date, the conditional formatting rule will respond instantly. By using other cells for input, and naming them as named ranges, you make the conditional formatting interactive and the formula is simpler and easier to read.
