formula in conditional formatting with dates

[MelB]

Cell A1 = doc status with the following possible values:
"Due", "Revise", "Sign", "Complete".

Cell A2 contains a date.

I need a formula for conditional formatting that will turn Cell A2 yellow
with blue bold font if A1="Due" or "Sign" AND Cell A2 is within 31 days of
today's date. Todays date is located in cell A3 as =TODAY().

I have used the conditional formatting function alot in very basic ways, but
am somehow just not able to work out this formula.

 

[Pete_UK]

Select A2 and click on Format | Conditional Formatting. In the
dialogue box select Formula Is rather than Cell Value Is, and enter
this formula:

=AND(OR(A1="Due",A1="Sign"),A2>TODAY()-31)

Click on the Format button to set your colours and Bold (Patterns tab
for background colour), then OK twice to exit the dialogue.

You don't need to use A3 (unless you want it for something else, in
which case put A3 instead of TODAY() in the formula).

Hope this helps.

Pete

 

[carlo]

Select A2 and click on Format | Conditional Formatting. In the
dialogue box select Formula Is rather than Cell Value Is, and enter
this formula:

=AND(OR(A1="Due",A1="Sign"),A2>TODAY()-31)

Click on the Format button to set your colours and Bold (Patterns tab
for background colour), then OK twice to exit the dialogue.

You don't need to use A3 (unless you want it for something else, in
which case put A3 instead of TODAY() in the formula).

Hope this helps.

Pete






- Show quoted text -

if you want to ignore dates that are in the future you can change the
formula to this:

=AND(OR(A1="Due",A1="Sign"),AND(A2>TODAY()-31,A2<=TODAY()))

hth

Carlo

 

[Roger Govier]

Hi Mel

Try
=AND(A2-A3<=31,A2-A3>0,OR(A1="Due",A1="Sign"))