Conditional Formatting based on month of today

[Jess709]

I am working on some conditional formatting based on a due date's relation to the current date. Here is what I'm trying to accomplish:

1. If the month of the due date (cell F4) is less than the month of the current date then highlight to red. This is what I used and it works fine: =MONTH(F3)<MONTH(TODAY())

2. If the month of the due date (cell F4) is the same month as the month of the current date then highlight in green. This is what I used and it works fine: =MONTH(F3)=MONTH(TODAY())

3. This is the problem item. If the month of the due date (cell F4) is greater than the month of the current date, BUT not more than 1 month greater than the current date, then highlight in yellow. For example, if the due date in cell F4 is 8/1/2017 and the current date is 3/3/2017 I don't want it to highlight that cell at all, because August is more than 1 month great than the current month. So in this scenario only dates in April would be highlighted.

Any help with the conditional formatting formula for #3 is greatly appreciated.

-Jessica

 

[63falcondude]

Try

=MONTH(F4)=MONTH(TODAY())+1

for yellow

 

[barry houdini]

=MONTH(F4)=MONTH(TODAY())+1

...but this won't work in December....

This formula should work for you and only highlight dates for the correct year

=EOMONTH(TODAY(),0)=EOMONTH(F4,-1)

 

[Jess709]

The last one worked perfect, thanks!!1

 

[63falcondude]

...but this won't work in December....

This formula should work for you and only highlight dates for the correct year

=EOMONTH(TODAY(),0)=EOMONTH(F4,-1)

That's right... didn't think of that.