I still can't get it right

[Elvey]

THis is my data, I need to set a reminder so I dont miss the deadlines

E5 F5
19 May 2006 15 November 2006
26 August 2005 22 February 2006
27 August 2005 23 February 2006

I plan the next date by E5+180, this works but when I try to use
conditional formating it fails.

I need to change to Green if +120 days and Red if 160 days

Sorry if I am being thick and thanks for your help

 

[Bob Phillips]

If what +120 equals what?

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

 

[RaymundCG]

Hi Elvy!

I am not quite sure if this exactly answers your question but you may try
the ff...

Supposing that E5 contains the present date (you may use =today() function)
and F5 is the deadline, use conditional formatting in E5 by...

Format > Conditional Formatting...(use 2 conditions)

Condition 1> select Formula Is.. then enter

=DATEDIF(TODAY(),F5,"d")=160 then apply desired format (color red)

then add another condition

Condition 2> select Formula Is.. then enter

=DATEDIF(TODAY(),F5,"d")=120 then apply desired format (color green)

then press OK.

In this case you have to set the deadline beforehand; today() just updates
the cell with the current date everytime you open the workbook.

Hope this helps!

 

[Gilles Desjardins]

Elvey.
Assuming your date is in A1 (notice the $ sign in the formula) and tha
A1+180 is in B1, highlight both columns.

Format, Conditional Formatting:

Condition 1 "Formula is" =IF((TODAY()-$A1)<160,TRUE,FALSE) choose green for
the formatting then ADD
Condition 2 "Formula is" =IF((TODAY()-$A1)>=160,TRUE,FALSE) choose red.

Gilles