Issues with RAG IF statement - formula wizz needed!

[el333]

I am using the following IF statement to flag how close to a consultation deadline I am (deadline given by date in F4 with RAG displayed in adjacent column G4):

=IF(F4<TODAY(),"RED",IF(F4>TODAY()+21,"GREEN","AMBER"))

Once I have responded to the consultation and added a response date in I4 is there a way turning the whole row or at least G4 blue to mark that it is completed and thus does not require attention - in effect can I add a condition to 'turn-off' the RAG?

Lastly, and just because its bothering me aesthetically (yeah I'm a perfectionist) I have applied the IF statement to the entire column of F (by using ctrl + D) and as such the column is now red by default - is there a way of making it so that RAG only appears when there is given date in F4?

Basically a formula novice and I've been trawling forums and making this work through trial and error - any help really appreciated!

Thanks

[BadlySpelledBuoy]

From one pedant to another, the below formula should cure the red cells in your second point.

Please Login or Register  to view this content.

In order to answer the first part it would be easier if we could see the workbook layout. Could you attach one?

BSB

[Pete_UK]

You can use conditional formatting to turn the rows blue. Select data in A4:I4, then click on CF | New Rule | Use a formula... , and put this formula in the dialogue box:

=$I4<>""

Click on the Format button | Fill tab and choose the shade of blue that you want, then OK twice to exit the dialogue box. Then you can use the Format Painter to apply that format to the other rows.

If you want to avoid RED if F4 is empty, you can do this:

=IF(F4="","",IF(F4<TODAY(),"RED",IF(F4>TODAY()+21,"GREEN","AMBER")))

then copy down.

Hope this helps.

Pete

[TMS]

Please Login or Register  to view this content.

Regards, TMS

[Kyle123]

Another way

Please Login or Register  to view this content.