HELP - Conditional Formatting Dates & Blank Cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two columns of data:
Column A (Current Review Date) Column B (Next Review Date (2 years out)
I want to conditionally format so that the reviews due within 30 days of
today are highlighted and the reviews overdue are also highlighted but that
cells containing no date remain unchanged. I have tried multiple variations
of the Today +30 and the =AND(B1<>"",B1<TODAY()) formulas with no success.
SEE BELOW:
Example Data:
Column A Column B
1. 9/1/05 9/1/07 (This would appear in RED)
2. 10/1/05 10/1/07 (This would appear in GREEN)
3. blank blank (This would be unaffected by the conditional format)
4. 1/5/06 1/5/08

PLEASE HELP!
 
=AND(B1<>"",B1>=TODAY(),B1<=TODAY()+30)

should do the first

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
I used 'Formula is' and pasted what you wrote below and it did not work. Any
other ideas?

I am thorougly frustrated.
 
Diane,
Instead of "Formula is", try "Cell Value is" option in the same drop down
list and use the following:

condition 1: less than =Today()
(Format: Font Bold, Color Red)
condition 2: between =Today() and =Today()+30
(Format: Font Bold, Color Green)

Pl tell me whether I have understood your requirement correctly.

Balan
 
What does 'did not work' mean exactly?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
I wanted to post what finally worked...and elimated the formatting of blank
cells (that was what was not working when I tried the originally sugested
solution)

Condition 1: cell value is: ="" (No format set)
Condition 2: formula is: =DAYS360(S3,TODAY())>0 (Format Fill Red & Bold)
Condtion 3: cell value is: =TODAY()+60 (Format Green)

Thanks to both of you for your help!!
 
Back
Top