Traffic Light warning system for schemes

[Smithy02468]

Morning.

my mind is all fuzzed, I have a worksheet with an ordered date, Traffic light colour cell, ordered date +30 days(green fill), ordered date +60 days(yellow fill), Ordered date +90 days(amber fill), date>90+days (red fill), Date scheme completed cells. There is a cell in the spreadsheet that has the current date to allow the traffic light colour cell to indicate the status of the scheme traffic light colour cell must be green when the date scheme completed is filled in. The =30,60,90,90+day columns will be hidden as these are the cut off dates for the traffic light cell on opening the workbook. Should the schem flag up as red then be completed, the traffic light cell needs to goto green when the completed cell date is entered and the adjacent cell dropdown list is selected as Completed.

Any help on this appreciated.
Regards
Colin

Current date		30/10/2020
Estimated Cost (£)	Scheme Order Date	Traffic Light	30days	60days	90days	90days+	Progress (Risk 2020/21)	Completed Date
4,026	30/09/2020	30/10/2020	30/10/2020	29/11/2020	29/12/2020	28/01/2021	DELIVERED	29/12/2020

 

[AlanY]

try this with conditional format

Book1.xlsm
	A	B	C	D	E	F	G	H	I
1	Current date		30/10/2020
2
3	Estimated Cost (£)	Scheme Order Date	Traffic Light	30days	60days	90days	90days+	Progress (Risk 2020/21)	Completed Date
4	4,026	27/06/2020	30/10/2020	30/10/2020	29/11/2020	29/12/2020	28/01/2021	DELIVERED	11/12/2020
Sheet4

Cell Formulas
Range	Formula
I4	I4	=TODAY()

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4	Expression	=OR((C1-B4)<=30,NOT(ISBLANK(I4)))	text	YES
C4	Expression	=(C1-B4)<=60	text	YES
C4	Expression	=(C1-B4)<=90	text	YES
C4	Expression	=(C1-B4)>90	text	NO

 

[Smithy02468]

Hi Alan,

Thanks for this, my mind was going all to pot on this, trying to grasp that dates are seen as numbers, the setup works fine but we might have two scenarios where the cell with the completion date is filled within the 90 days or after 90 days it needs to show green in the traffic light cell. I would like to keep the current date in C1 is the "= today" to check against the 30, 60 and 90 for when we open the worksheet to check on progress, the date entered in i4 will be the scheme completed date which will override the traffic sign colours to green.

Thanks again in advance for any help you can give me on this.

Colin

 

[Smithy02468]

Alan,

sorted it, it seems I for some reason missed off the Not(ISBLANK(I4))) in the first line of conditional formatting and changed the red format to =AND((J2-I5)>=90,OR(ISBLANK(P5))) in my spread sheet which works a treat thank to your contribution.

Many thanks again
Regards
Colin

 

[AlanY]

glad that you got it sorted