# Red Yellow Green Gray

Hi Experts :),

I need help with my formula.

Principles I am trying to achieve are:

• if Min Variance column is blank AND the Minister Due Date is past today by less than 2 days, OR the Min Variance@row is Less than 2, then flag Yellow.
• If the Min Variance column is blank, AND the Minister Due Date is past today by more than 2 days OR the Min Variance@row is greater than 2, then flag Red
• If the Min Variance column is blank AND the Minister due date has not yet been reached OR the Min Variance @row is less than or equal to 0, then flag Green
• If no conditions are met flag gray.

I am very stuck on the brackets. But there may be a simpler way?

= IF(ISBLANK(AND([Min Variance]@row), [Minister Due Date]@row > TODAY(3), OR[Min Variance]@row >= 2)), "Red", IF(ISBLANK(AND(OR([Min Variance]@row), [Minister Due Date]@row > TODAY, [Min Variance]@row < 2))),"Yellow", IF(ISBLANK(AND([Min Variance]@row), [Min Due Date]@row <=0, "Green", "Gray"))

Tags:

• Thank you so much. This helped a lot.

I made a small change to your above formula (less than and equal to) and it is working a treat.

=IF(OR(AND(ISBLANK([Min Variance]@row), TODAY() <= [Minister Due Date]@row), [Min Variance]@row <= 0), "Green", IF(OR(AND(ISBLANK([Min Variance]@row), TODAY() <= [Minister Due Date]@row), [Min Variance]@row <= 2), "Yellow", IF(OR(AND(ISBLANK([Min Variance]@row), TODAY() <= [Minister Due Date]@row), [Min Variance]@row >= 2), "Red", "Gray")))

I just need to make this formula work. I subtract 1 to account for when the dates are the same I dont want it to record as being late. But when it the task is delivered early it subtracts -1 to a negative number making it look an extra early.

=IFERROR(NETWORKDAYS([Minister Due Date]@row, [Minister Actual Date]@row) - 1, "")

So i need a formula that says networkdays between two dates but don't count the either date as a day I think.

hahaha formula fun.

Thank you for your help :)

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!