Formula Assistance

CSmall1122

I am attempting to do the following:

1. If status is Complete or N/A = Green
2. If start date is within 5 days or past due and status is not Complete or N/A = Red
3. If start date is more than 5 days and status is not Complete or N/A = Yellow

I have the following formula, but no luck. Any help is greatly appreciated.

=IF(OR(Status@row = "Complete", Status@row = "N/A"), "Green", IF([Start Date]@row <= TODAY(5), "Red", IF(Status@row <> "Complete", Status@row <> "N/A", "Yellow", "Green")))

Carson Penticuff

If I am understanding this correctly, you can simplify the formula quite a bit.

=IF(OR(Status@row = "Complete", Status@row = "N/A"), "Green", IF([Start Date]@row <= TODAY(5), "Red", "Yellow"))

CSmall1122

Thanks Carson, this almost gets me where I want to be. For the Yellow, I want it to indicate even on rows without a start date and status is not Complete or N/A. This is where I'm struggling. Any tips on this? @Carson Penticuff

Nick Korna

Hi @CSmall1122,

This should work for you:

=IF(OR(Status@row = "Complete", Status@row = "N/A"), "Green", IF(AND(OR([Start Date]@row > TODAY(5), ISBLANK([Start Date]@row)), OR(Status@row <> "Complete", Status@row <> "N/A")), "Yellow", "Red"))

Hope this helps; if you've any questions or problems with it then just post! 😊