HELP - Conditional Formatting Dates & Blank Cells
 

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!

HELP - Conditional Formatting Dates & Blank Cells
 

=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)

"Diane1477" wrote in message
...
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!

HELP - Conditional Formatting Dates & Blank Cells
 

I used 'Formula is' and pasted what you wrote below and it did not work. Any
other ideas?

I am thorougly frustrated.

"Bob Phillips" wrote:

=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)

"Diane1477" wrote in message
...
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!

HELP - Conditional Formatting Dates & Blank Cells
 

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
"Diane1477" wrote:

I used 'Formula is' and pasted what you wrote below and it did not work. Any
other ideas?

I am thorougly frustrated.

"Bob Phillips" wrote:

=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)

"Diane1477" wrote in message
...
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!

HELP - Conditional Formatting Dates & Blank Cells
 

What does 'did not work' mean exactly?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Diane1477" wrote in message
...
I used 'Formula is' and pasted what you wrote below and it did not work.
Any
other ideas?

I am thorougly frustrated.

"Bob Phillips" wrote:

=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)

"Diane1477" wrote in message
...
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!

HELP - Conditional Formatting Dates & Blank Cells
 

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!!
"Balan" wrote:

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
"Diane1477" wrote:

I used 'Formula is' and pasted what you wrote below and it did not work. Any
other ideas?

I am thorougly frustrated.

"Bob Phillips" wrote:

=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)

"Diane1477" wrote in message
...
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!