RE: A better explanation of help required

Giganews Newsgroups
Subject: RE: A better explanation of help required
Posted by:  RaymundCG
Date: Sat, 20 May 2006

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!

--
Thanks and kind regards

"Elvey" wrote:

> 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

Replies

In response to

A better explanation of help required posted by Elvey on Sat, 20 May 2006