A better explanation of help required

[Guest]

I have to assess my team of 70 every six months so if the 1st assessment is
01.01.06 then the next is due on the 01.12.06.

I now how to show the next date due by taking the cell +180 to give me the
next assessment date.

What I want to do is get a reminder 120 days (Coloured Green) from the
original assessment date and then a final reminder at 160 days(Coloured Red )
so I do not miss their assessments

I have tried to use conditional formating but I do not know how to get it to
work as each original assessment dates are different depending when they
joined the team.

How do I get Excel to calculate "X" amount of days forward and change colour
to give me a visual reminder without trawling through the spreadsheet.

Column E Column F
Original Dates Dates in six months time

19 May 2006 15 November 2006
26 August 2005 22 February 2006
27 August 2005 23 February 2006
28 August 2005 24 February 2006
29 August 2005 25 February 2006
30 August 2005 26 February 2006

Any help will be most appreciated

Elvey

 

[Guest]

Hi again!

One work around is to set up a third column to calculate the days left
before the assessment date. You may use the ff formula

=DATEDIF(TODAY(),assessment date,"d") please note that this function is
valid only until the assessment date

then based on the results you may now apply conditional formatting using two
conditions.

Condition 1>Cell Value Is... Less than or equal to 120 > apply the desired
format (green)

Condition 2>Cell value Is... Between 121 and 160 > apply the desired format
(red)

These conditional formatting may be applied either to the DATEDIF results
cells or to another column(?) before the original dates containing the
employee name.

Hope this helps!

 

[Guest]

Modification to my earlier post (after reading again your explanation)
formula should be...

=DATEDIF(original date,today(),"d")

Conditional formating still the same.

 

[davesexcel]

highlight the cells you want to conditional format
select conditional format
select, if cell value is greater than or equal to,
then select the cells you want to compare to
then select the condition
then click add
and do it again but this time select less than, and select the
condition you want then press ok

the one cell should have the formula =today()