Hi Fred

I am currently using 3 seperate conditions as follows:

TO TURN CELL GREEN =DATEDIF(TODAY(),$N$162,"M")=3

TO TURN CELL ORANGE =AND(DATEDIF(TODAY(),$N$162,"M")<3,TODAY()<$N$162)

TO TURN CELL RED=TODAY()=$N$162

I want to modify these so it is not always based on 3 months, I need it be
12-months if H162 says EU, 6-months if it says TENDER, 3 months for
QUOTATIONS & 3 months for BUSINESS CASE.

I hope you can help me get this right!

Thanks
Vikki

"Fred Smith" wrote:

It would help if you showed the formula you were using.

In general, you do something like:
=or(and(a1="EU",a2<today()+365),and(a1<"EU",a2<to day()+91))

Regards,
Fred.

"Vikki" wrote in message
...
I have a spreadsheet which uses conditional formattting. The colour of a
particular cell changes colour, depending on the expiry date (shown in
another cell).

I have another column with wording in which says either, Quotes; Business
Case; Tender; EU. At the moment, cell A7 turns amber, when the expiry date
is
3-months away, to alert me to begin renewal of a contract, however, some
contracts require upto 12-months of preparatory work, so is it possible,
for
the formula in A1 to take into account, the type of renewal (ie. Quotes;
tender etc).

For example, if a contract expires 01.01.11, and the renewal cell states
EU,
then I want the formula to pick this up, and turn amber 12-months prior to
the expiry date, ie on 01.01.10.

Does this make sense and can anyone please help??

Many thanks
Vikki