Conditional formatting based on dates (month and year specifically)

[mhewitson15]

I am trying to create some conditional formatting that will highlight a cell yellow if the month and year matches the current month and year, and will highlight blue if the month/year is equal to NEXT month. I've figured out how to get the cell to highlight if it matches the current month/year, but not the second part, where it will highlight blue if the date matches next month.

The formula I have used to highlight if the month/year matches the current month/year is
=(DATE(YEAR($Q$2),MONTH($Q$2),1)-DATE(YEAR(G4),MONTH(G4),1)=0)
where cell Q2 contains the formula =TODAY() and cell G4 is the date I wish to compare.

However, I cannot figure how to alter this formula to get cell G4 to highlight blue if and only if the year and month match NEXT month (so in this case April 2015 but not may 2015).
=(DATE(YEAR($Q$2),MONTH($Q$2),1)-DATE(YEAR(G4),MONTH(G4),1)=-1 doesn`t work.

[FDibbins]

For the "same" month, try this instead...
=EOMONTH(G4,0)=EOMONTH(Q2,0)

For "next" month...
=EOMONTH(G4,0)=EOMONTH(Q2,1)

[mhewitson15]

That worked perfectly and made it simpler for the other formula as well. Thank you!

[FDibbins]

Happy to help

[mhewitson15]

I tried adding reputation for you but its telling me I have to spread some reputation to others before I can give you more. Thanks again!

[FDibbins]

Thats OK, thanks for trying