MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Conditional format a due date


Posted by Moniqe on October 27, 2001 7:22 AM

I have the formula
=DATE(YEAR(A1),MONTH(A1),DAY(A1)+30)

I want the date due to change to green when it gets with in 10 days. Red when it gets to 5 days. How do I apply a conitional format to accomplish this?


Posted by Aladin Akyurek on October 27, 2001 11:25 AM

Monique,

I'll assume that the first due date is in A1.

Select the range of the due dates.
Activate Format|conditional Formatting.
Select "Formula Is" for Condition 1.
Enter as formula

=AND(A1>=TODAY(),A1-TODAY()<=5)

Activate Format.
Select red on the Patterns tab.
Click OK.
Activate Add.
Select "Formula Is" for Condition 2.
Enter as formula

=AND(A1>=TODAY(),A1-TODAY()<=15)

Activate Format.
Select green on the Patterns tab.
Click OK.
Click OK.

Aladin

Posted by Monique on October 27, 2001 7:56 PM

Thanks

Thanks