RYG Status formula question

johnna.young

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?

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

johnna.young

@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

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