If Statement

[nobbsy]

Book1
	A	B	C	D
1	Today'sDate	3-Oct-06
2		Priority	ForcastCompletion	TrafficLight
3	Task1	Priority	1-Oct-06	Red
4	Task2	NonPriority	12-Dec-06	Green
5	Task3	NonPriority	1-Sep-06	Orange
6	Task4	Priority	13-Nov-06	Green
Sheet1

I would like the following in a formula
Green = Today's date is Less Than the Forecast Completion Date
Red = Greater than the Forecast Completion Date and is a priority
Orange = Greater than the Forecast Completion Date but is a non priority.
Thanks

 

[PATSYS]

I would like the following in a formula
Green = Today's date is Less Than the Forecast Completion Date
Red = Greater than the Forecast Completion Date and is a priority
Orange = Greater than the Forecast Completion Date but is a non priority.
Thanks

In cell D3, type this formula:

=If($B$1<C3,"Green",if(and($B$1>C3,B3="Priority")=true,"Red",if(and(($B$1>C3,B3="Non Priority")=true,"Orange")))

Then copy down.

 

[Brian from Maui]

Try,

=IF($B$1 < C3,"green",IF(AND($B$1 > C3,B3="priority"),"red","orange"))

If you want a visual of the colors, try Conditional Formatting

 

[PATSYS]

Why is the formula I typed looks incorrect in the post ut when I try to change it, it looks correct in the edit mode?