Formula for RYGB Status

Hi, I'm currently using this formula for status health but it's not the best because "Not Started" status is not showing Red if the End Date for the task is in the past. Obviously, the task is overdue even if it hasn't started. Can anyone assist to make this formula better?

=IF(Status@row = "Complete", "Blue", IF(Status@row = "Canceled", "", IF(Status@row = "Not Started", "", IF(ISBLANK([End Date]@row), "Blue", IF([End Date]@row >= TODAY(), "Green", IF([End Date]@row >= TODAY() - 3, "Yellow", "Red"))))))

Best Answers

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 01/31/22

    @tlong2b

    The reason this is happening is because as the system is evaluating your IFs, when it finds the status = Not Started, it applies the positive condition for that nested IF --blank-- and stops evaluating. Since you're wanting to evaluate on two criteria columns (Status and End Date), using some ORs and ANDs might help, but you have to figure out which criteria are most important and put them first.

    =IF(OR(Status@row = "Complete", ISBLANK([End Date]@row), "Blue", IF(Status@row = "Canceled", "", IF([End Date]@row >= TODAY(), "Green", IF(AND([End Date]@row >= TODAY(-3), Status@row <> "Complete"), "Yellow", IF(OR(Status@row = "Not Started", [End Date]@row < TODAY(-3), "Red")))))

    If the Status is Complete or End Date is blank, make it blue;

    otherwise, If the Status is Canceled, make it blank;

    otherwise, if the End Date is today or after today, make it green;

    otherwise, if the End Date is equal to or after three days ago (but not today or after today), and the status is NOT Complete, make it yellow;

    otherwise, if the status is Not Started or the End Date is earlier than 3 days ago, make it red.

    Note: Use the Smartsheet parentheses color coding to make sure I have the right number of them at the end there!

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • tlong2b
    tlong2b ✭✭
    edited 02/01/22

    @Jeff Reisman Thank you Jeff for the explanation! I used your formula and I get a #Incorrect Argument Set. I'm thinking there's a missing ) or (. It looks like there are enough parentheses at the end though. I'm not proficient at this so your help is appreciated.

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    Looks like I'm missing one right near the end, after TODAY(-3)!

    [End Date]@row < TODAY(-3)), "Red")))))

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • tlong2b
    tlong2b ✭✭

    @Jeff Reisman I was so hoping this would be the resolution. It doesn't seem to turn red for those items which are "Not Started" and overdue. Also, I don't have anything for "In Progress" though that's my fault I didn't state I needed that in the beginning. Thanks again!

  • tlong2b
    tlong2b ✭✭
    edited 02/01/22

    @Jeff Reisman I messed up in last response - actually the formula still shows #Incorrect Argument Set or #Unparseable, depending on how many parentheses at the end - I've tried adding and taking away. I don't know what's wrong.

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    edited 02/01/22

    You might have to just play around with the order or things in your logic.

    Since you are using a lot of criteria, you could also create a "Helper" column to combine a text value representing the end date criteria with the status:

    =IF([End Date]@row >= TODAY(), "ONT", IF([End Date]@row >= TODAY(-3), "DUE", IF(ISBLANK([End Date]@row), "NON", IF([End Date]@row < TODAY(-3), "LAT")))) + " " + Status@row

    Then, based on your logic, you lay out a grid with what color each End Date/Status combo should be, allowing you to group results together in your big IF formula. (I added "Canceled" in here for anything with a status of Canceled, to differentiate those from rows with no end date.)

    ONT Complete = Blue

    ONT In Progress = Green

    ONT Not Started = Green

    ONT Canceled = Canceled

    DUE Complete = Blue

    DUE In Progress = Yellow

    DUE Not Started = Yellow

    DUE Canceled = Canceled

    LAT Complete = Blue

    LAT In Progress = Red

    LAT Not Started = Red

    LAT Canceled = Canceled

    NON Complete = Blue

    NON In Progress = ""

    NON Not Started = ""

    NON Canceled = Canceled

    =IF(Status@row = "Complete", "Blue", IF(OR(Helper@row = "ONT In Progress", Helper@row = "ONT Not Started"), "Green", IF(OR(Helper@row = "DUE In Progress", Helper@row = "DUE Not Started"), "Yellow", IF(OR(Helper@row = "LAT In Progress", Helper@row = "LAT Not Started"), "Red", IF(OR(Helper@row = "NON In Progress", Helper@row = "NON Not Started"), "", IF(Status@row = "Canceled", "Canceled"))))))

    I think the order makes sense here:

    Anything with status of complete is blue,

    On time AND in progress or on time AND not started is green,

    Due in the past three days AND in progress or Due in the past three days AND not started is yellow,

    Late AND in progress or Late AND not started is red,

    No end date AND in progress or no end date AND not started is blank,

    Anything with status of canceled is Canceled.

    All this is tested and working:


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    Here it is in the same order as the criteria grid:


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • tlong2b
    tlong2b ✭✭

    Oh my gosh. I don't know what you did, but it works! Thank you, thank you! This is gold.

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    I just kept working the problem. Using helper columns to handle some of the functions outside of your main formula is key to making a lot of more complex things work in Smartsheet. Writing out a list of all possible combinations of your criteria really helps with getting a good idea of the right order for your nested IF statements. This trick of combining two disparate criteria (date and text) into one alphanumeric value is one I've used on quite a few Smartsheet projects.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!