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"))


Really appreciate your help.

Tags:

Best Answer

Answers

  • 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!