|Subject:||RE: A better explanation of help required|
|Date:||Sat, 20 May 2006|
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
Condition 1>Cell Value Is... Less than or equal to 120 > apply the desired
Condition 2>Cell value Is... Between 121 and 160 > apply the desired format
These conditional formatting may be applied either to the DATEDIF results
cells or to another column(?) before the original dates containing the
Hope this helps!
Thanks and kind regards
> 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
A better explanation of help required posted by Elvey on Sat, 20 May 2006