Trouble w/ conditional formatting and dates

[Studebaker]

Hello,

I'm having trouble with a formula in conditional formatting.

Column D has an expiration date (date a pledge expires) and Column E will be
blank, but Column E will be where I want my conditional formatting. I want
Column E to say in bold, red letters, CALL CLIENT!, when today's date is less
than or equal to 3 days before the expiration date in Column D.

The conditional formula I had in Column E was FORMULA IS:
=IF((TODAY()<=$D$1)-3, "CALL CLIENT!") and then I had it format the font to
be bold, red if statement is true. This doesn't work. Can someone help?

Thank you very much!
Studebaker

 

[Sheeloo]

Put this in E1
=IF(TODAY()<=(D1-3), "CALL CLIENT!","")

then use this in FORMULA IS for conditional formatting for cell E1
=TODAY()<=(D1-3)

You can then paint this on other cells in Col E

If you want to compare all cells in E with D1 then change D1 to $D$1...

 

[Rick Rothstein]

Instead of making the additional call to the TODAY function in the
Conditional Formatting formula, I think you can just use this "Formula Is"
formula for the Conditional Formatting in E1...

=E1<>""

 

[Studebaker]

I'm sorry but it is not working. The cell doesn't do anything. Do you think
the <=(D1-3) part is correct? B/c I want to say if today is 3, 2, or 1 days
before the expiration date or on the expiration date then say "Call Client".
Can you think of anything?

Thanks for the reply.

 

[Rick Rothstein]

Try this formula...

=IF(ABS(D1-1.5-TODAY())<=1.5, "CALL CLIENT!","")

and use my

=E1<>""

Conditional Format formula in the "Formula Is" field.

 

[Shane Devenshire]

Hi,

In cell E2 enter the following formula and copy it down as necessary:

=IF(TODAY()-D2<=3,"Call Client","")

Select the range with these formulas and click the Bold button, choose Red
from the Font color drop down.

There is no need for condtional formatting!