PLS HELP - Conditional Format w/Dates

[Jacki]

I need help - I am trying to set up conditional formatting to show status
using the red, green and yellow "Stop Light". I have three conditions, but I
am having troubles setting the formulas for them.

1. For the 1st condition, I want to show that if the action (Cell F2) is
progressing beyond today, and is less than 45 days before the target
completion date (Cell H2), then it is GREEN

2. For the 2nd, it would be if the action in cell F2 is greater than today,
but within 45 days from the target completion date in Cell H2, then it would
be yellow

3. For the 3rd, it the action in Cell F2 is equal to or greater than today,
then it would be red.

Someone please tell me how to set these formats - I could tell you what I
have, but it would not be pretty - I need help for a school project. Thanks!

 

[Daniel.C]

Paste the following formula in the cell you want to format :
=IF(AND(F2<TODAY(),H2-F2>45),3,IF(AND(F2>TODAY(),H2-F2<45),2,IF(F2>=TODAY(),1,0)))
In the conditional format, choose the spotlights, modify the rule :
Green if value >=3, yellow if value >=2, else red.
HTH
Daniel

 

[Satti Charvak]

Hi Jacki,
Conditional formatting is ok. I can give you the process and the formula
but the issue is the today's date.

Now after we have put the conditional format for today, the same excel sheet
may change when you open it tomorrow as the current date changes. Is it ok
for you?

or do you want to mention any specific date for reference.

 

[Jacki]

Hi Satti:

I guess it would be ok to use TODAY as the formula, so that it becomes the
factor by which my formula will know if the action is within 45 days,
overdue. etc. I'd appreciate any help you can give me. Thanks

 

[Jacki]

Daniel,

Thank you for your reply and your help- and please forgive my ignorance: is
this whole formula pasted into one condition, or is this the formula for all
three conditions? If so, where is the break for the other conditions?
Please advise, and in the meantime, I'm going to try to work it.

 

[Daniel.C]

I assumed you wanted to use the stop lights of XL2007 conditional
format. If it's not the case, please forget my answer. This formatting
is based on the values of the cell, so you have to enter the formula in
the cell to which you want to apply a conditional formatting.
Daniel

 

[Jacki]

No Daniel, actually, I am using Excel 2003. Thanks anyway Daniel - I do
appreciate your efforts. Jacki

 

[Daniel.C]

Then use the 3 formulae :
=AND(F2<TODAY(),H2-F2>45) (red)
=AND(F2>TODAY(),H2-F2<45) (yellow)
=F2>=TODAY() (green)
Note that the 2 last ones may be both true. You'll have to decide which
none to apply in that case, or use :
=AND(F2>TODAY(),H2-F2>=45) (green)
for the third case.
Daniel