conditional formatting for dates in future and past

[angelik]

Hello there

Let me provide an example of what I'm trying to do.

I have a deadline of 30 June 2015. I want the cell this date is in to highlight red when it is less than 30 days away and if it has past without the cell next to saying it has been met.

So I have

=30<TODAY (format red) for the impending and past dates
=A1="YES"

and this works fine. However, all dates 30+plus days in the future are also formatted. How can I get these to stay without formatting?

Thanks

 

[Stridhan]

perhaps, change +29 to suite number of days.

A1 = deadline, B1 current date, C1 "next cell telling YES" if met

=AND(B$1+29>=$A$1,C$1<>"YES")