Symbol Formula using date and another field

D Gray

I need to create a formula based on the following criteria:

1) Contract Type is...Lease....then the following needs to be assessed in the Target Completion Date column.

2) Target Completion Date is more than 70 days out "Green"

3) Target Completion Date is 20 days out or less "Yellow"

4) Target Completion Date is in the past "Red"

I was successful in building the formula based on the target completion date, but when I try to incorporate the Contract Type criteria I keep getting error messages. Here is my current formula.

=IF(ISDATE([Target Completion Date (SLA)]@row), IF([Target Completion Date (SLA)]@row <= TODAY(), "Red", IF([Target Completion Date (SLA)]@row < TODAY(20), "Yellow", "Green")))

Genevieve P.

Hi @D Gray

Try this:

=IF([Contract Type]@row = "Lease", IF([Target Completion Date (SLA)]@row <= TODAY(), "Red", IF([Target Completion Date (SLA)]@row < TODAY(20), "Yellow", "Green")))

This will be blank if the Contract Type is not "Lease", is that what you'd like? Also, is there a possibility that the Target Completion Date could be blank? We can add in that criteria near the beginning, too:

=IF([Contract Type]@row = "Lease", IF([Target Completion Date (SLA)]@row = "", "", IF([Target Completion Date (SLA)]@row <= TODAY(), "Red", IF([Target Completion Date (SLA)]@row < TODAY(20), "Yellow", "Green"))))

Cheers,

Genevieve