Conditionally format a cell based on the date in another cell

[knham1987]

I am putting together a tracking spreadsheet whereby we have tasks that need to be done in relation to an end date.

For example, a welcome email needs to be sent 3 weeks before the date. If the cell is still blank TWO weeks before and up to the date of, I'd like the cell to be yellow. On or after the date, I'd like it to go red. What I need is the formula to use with my conditional formatting rules (I assume I need two) that will tell excel to use the color [DATE]-21 days inclusive and [DATE]+any days.

Can I do this in Excel?

Thanks in advance!

Kelly

 

[Joe4]

Welcome to the Board!

You should be able to use Conditional Formatting to do this, but we will need to know a few details, such as:
1. What cell this "end date" is in?
2. What is the cell containing the date that you want to conditional format?

So we are looking for cell addresses for both of those questions.

 

[knham1987]

In Column F, F4 should reference F2's date and, if TODAY is 21 days before F2, no color, if TODAY is ,= 20 days before F2, yellow, if TODAY is greater than or equal to F2, RED

Right now, I one formula: =TODAY()>=$F$2+20 (yellow) and this is working but I can't get one for RED to work...

Thanks!

 

[Joe4]

If I understand you properly, this should be the Conditional Formatting formula for the red fill:

=AND(F4="",TODAY()>F$2)

and this should be the Conditional Formatting formula for the yellow fill:

=AND(F4="",(TODAY()+21)>F$2)

And be sure to have the red rule run first in the order of Conditional Formatting formulas, and "Stop if true"