RYG Status formula question

Hello. I'm currently solving for this: If the status says Complete, then blue. If the status says At Risk, or In Progress then calculate the RYG status based on the following parameters:

  • If the Target End date is 15 or more days from today, it's green.
  • If the Target End date is 4-14 days from today, it's yellow.
  • If the Target end date is equal to today, less than 3 days from today or in the past it's red.

This is the formula I'm using:

=IF(Status@row = "Complete", "Blue", IF(AND(OR(Status@row = "At Risk", Status@row = "In Progress"), [Target End Date]@row >= TODAY(15)), "Green", IF(AND(OR(Status@row = "At Risk", Status@row = "In Progress"), [Target End Date]@row >= TODAY(4)), "Yellow", IF(AND(OR(Status@row = "At Risk", Status@row = "In Progress"), [Target End Date]@row <= TODAY(3)), "Red", ""))))

I would like to add the following to the above formula:

  • If the status is "On Hold" then yellow.
  • If the status is "Not started" and the target start date is in the future, it is green. If the status is "Not Started" and the target start date has past, it's red.

Can anyone help me with adding that to the above formula, please?

Answers

  • John Shane
    John Shane ✭✭✭✭

    @johnna.young

    Seven nested IF statements. That's a lot but should handle your conditions.

    = IF(AND(Status@row = “Not started”, [Target Start Date] < TODAY()), “Red”, IF(AND(Status@row = “Not started”, [Target Start Date] >= TODAY()), “Green”, IF(Status@row = “On Hold”, “Yellow”, IF(Status@row = "Complete", "Blue", IF(AND(OR(Status@row = "At Risk", Status@row = "In Progress"), [Target End Date]@row >= TODAY(15)), "Green", IF(AND(OR(Status@row = "At Risk", Status@row = "In Progress"), [Target End Date]@row >= TODAY(4)), "Yellow", IF(AND(OR(Status@row = "At Risk", Status@row = "In Progress"), [Target End Date]@row <= TODAY(3)), "Red", "")))))))

    Cheers,

    John

  • @John Shane when I copy and paste the formula it says unparseable but I added the @row to the target start dates and it's saying invalid operation. any ideas?

  • John Shane
    John Shane ✭✭✭✭
    edited 01/05/24

    @johnna.young

    Not quite sure what the problem is/was, but I just worked it out again in my test bed. I tried to copy/paste, but Smartsheet did not like that. I validated the formula below.

    The formula below should match what you are trying to do.

    =IF(AND(Status@row = "Not started", [Target Start Date]@row < TODAY()), "Red", IF(AND(Status@row = "Not started", [Target Start Date]@row >= TODAY()), "Green", IF(Status@row = "On Hold", "Yellow", IF(Status@row = "Complete", "Blue", IF(AND(OR(Status@row = "At Risk", Status@row = "In Progress"), [Target End Date]@row >= TODAY(15)), "Green", IF(AND(OR(Status@row = "At Risk", Status@row = "In Progress"), [Target End Date]@row >= TODAY(4)), "Yellow", IF(AND(OR(Status@row = "At Risk", Status@row = "In Progress"), [Target End Date]@row <= TODAY(3)), "Red", "")))))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!