conditional formatting formula
 

I need to work out quite a complicated formula for conditional
formatting.

I have column B which will contain a given date.
Column C which will contain another date.
column D which may contain one of "MJ", "MN", "L+E", "S" or "T".


What I need to do is colour column D Green if ANY of the following
statements is true
D1="MJ" AND C1-B1=540
D1="MN" AND C1-B1=222
D1="L+E" AND C1-B1=90
D1="T" AND C1-B1=56
D1="S" AND C1-B1=51

then i can simply adjust those numbers down for yellow and again for
red once i have the formula in place.

Basically a different amount of days notice must be given for each of 5
different types of work (symbolised by the 5 abbreviations above). B
is the date it has been applied for, C is the date they plan to start.
So I need to colour the cell green if the cell contains xx type of work
AND the number of days between B and C is greater than the required
notice (which would be 540 if the cell contained "MJ" for example). So
the formula needs to check which type of work it is, and then check
that enough days notice for that work has been given, if it has then
itll be green.

Thanks


---
Message posted from http://www.ExcelForum.com/

conditional formatting formula
 

Hi
try the following formula
=(D1="MJ")*(C1-B1=540)+(D1="MN")*(C1-B1=222)+(D1="L+E")*
(C1-B1=90)+(D1="T")*(C1-B1=56)+(D1="S")*(C1-B1=51)


-----Original Message-----
I need to work out quite a complicated formula for
conditional
formatting.

I have column B which will contain a given date.
Column C which will contain another date.
column D which may contain one of "MJ", "MN", "L+E", "S"
or "T".


What I need to do is colour column D Green if ANY of the
following
statements is true
D1="MJ" AND C1-B1=540
D1="MN" AND C1-B1=222
D1="L+E" AND C1-B1=90
D1="T" AND C1-B1=56
D1="S" AND C1-B1=51

then i can simply adjust those numbers down for yellow
and again for
red once i have the formula in place.

Basically a different amount of days notice must be given
for each of 5
different types of work (symbolised by the 5
abbreviations above). B
is the date it has been applied for, C is the date they
plan to start.
So I need to colour the cell green if the cell contains
xx type of work
AND the number of days between B and C is greater than
the required
notice (which would be 540 if the cell contained "MJ" for
example). So
the formula needs to check which type of work it is, and
then check
that enough days notice for that work has been given, if
it has then
itll be green.

Thanks


---
Message posted from http://www.ExcelForum.com/

.

conditional formatting formula
 

An alternative

=MATCH(D1,{"S","T","L+E","MN","MJ"},0)=(5-MATCH(C1-B1,{539,221,89,55,50},-1)
)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"neowok " wrote in message
...
I need to work out quite a complicated formula for conditional
formatting.

I have column B which will contain a given date.
Column C which will contain another date.
column D which may contain one of "MJ", "MN", "L+E", "S" or "T".


What I need to do is colour column D Green if ANY of the following
statements is true
D1="MJ" AND C1-B1=540
D1="MN" AND C1-B1=222
D1="L+E" AND C1-B1=90
D1="T" AND C1-B1=56
D1="S" AND C1-B1=51

then i can simply adjust those numbers down for yellow and again for
red once i have the formula in place.

Basically a different amount of days notice must be given for each of 5
different types of work (symbolised by the 5 abbreviations above). B
is the date it has been applied for, C is the date they plan to start.
So I need to colour the cell green if the cell contains xx type of work
AND the number of days between B and C is greater than the required
notice (which would be 540 if the cell contained "MJ" for example). So
the formula needs to check which type of work it is, and then check
that enough days notice for that work has been given, if it has then
itll be green.

Thanks


---
Message posted from http://www.ExcelForum.com/