Traffic Light Filter On Dates
 

Hi following previous guidence I need advice on developing filters for
Conditional Monitoring to discriminate between:
Date One earlier than or equel to Date Two - condition green;
Date One earlier than or equel to Date Three - condition green;
Date One later than Date three & Date three earlier than Date two -
condition Red;
Date One later than Date Three & Date Three later than Date Two - condition
Amber. Where Date One is the schedule date; Date Two is the Data date; & Date
Three baseline date. Dates One & Three are different for every occurance and
Date Three is fixed for every period.

Traffic Light Filter On Dates
 

The formula


=IF(OR(A2<=B2,A2<=C$1),"Green",IF(AND(A2C$1,C$1<B 2),"Red",IF(AND(A2C$1,C$1B2),"Amber","Unspecifie d")))

is as per your request, with Date 3 in C1 and dates 1 & 2 in A2 & B2
etc.

Hope this helps

--

JPG Wrote:
Hi following previous guidence I need advice on developing filters for
Conditional Monitoring to discriminate between:
Date One earlier than or equel to Date Two - condition green;
Date One earlier than or equel to Date Three - condition green;
Date One later than Date three & Date three earlier than Date two -
condition Red;
Date One later than Date Three & Date Three later than Date Two -
condition
Amber. Where Date One is the schedule date; Date Two is the Data date;
& Date
Three baseline date. Dates One & Three are different for every
occurance and
Date Three is fixed for every period.


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=533183

Traffic Light Filter On Dates
 

Many thanks Bryan. Spreadsheet now works.
cheers
jack

"Bryan Hessey" wrote:


The formula


=IF(OR(A2<=B2,A2<=C$1),"Green",IF(AND(A2C$1,C$1<B 2),"Red",IF(AND(A2C$1,C$1B2),"Amber","Unspecifie d")))

is as per your request, with Date 3 in C1 and dates 1 & 2 in A2 & B2
etc.

Hope this helps

--

JPG Wrote:
Hi following previous guidence I need advice on developing filters for
Conditional Monitoring to discriminate between:
Date One earlier than or equel to Date Two - condition green;
Date One earlier than or equel to Date Three - condition green;
Date One later than Date three & Date three earlier than Date two -
condition Red;
Date One later than Date Three & Date Three later than Date Two -
condition
Amber. Where Date One is the schedule date; Date Two is the Data date;
& Date
Three baseline date. Dates One & Three are different for every
occurance and
Date Three is fixed for every period.


--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=533183

Traffic Light Filter On Dates
 

Good to see, and thanks for your response.

--

JPG Wrote:
Many thanks Bryan. Spreadsheet now works.
cheers
jack

"Bryan Hessey" wrote:


The formula



=IF(OR(A2<=B2,A2<=C$1),"Green",IF(AND(A2C$1,C$1<B 2),"Red",IF(AND(A2C$1,C$1B2),"Amber","Unspecifie d")))

is as per your request, with Date 3 in C1 and dates 1 & 2 in A2 & B2
etc.

Hope this helps

--

JPG Wrote:
Hi following previous guidence I need advice on developing filters
for
Conditional Monitoring to discriminate between:
Date One earlier than or equel to Date Two - condition green;
Date One earlier than or equel to Date Three - condition green;
Date One later than Date three & Date three earlier than Date two
-
condition Red;
Date One later than Date Three & Date Three later than Date Two -
condition
Amber. Where Date One is the schedule date; Date Two is the Data
date;
& Date
Three baseline date. Dates One & Three are different for every
occurance and
Date Three is fixed for every period.


--
Bryan Hessey

------------------------------------------------------------------------
Bryan Hessey's Profile:
http://www.excelforum.com/member.php...o&userid=21059
View this thread:
http://www.excelforum.com/showthread...hreadid=533183




--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=533183