RYGB health changes on date and status

=IF(ISBLANK([End Date]@row), "", IF(Status@row = "Complete", "Blue", IF(NETWORKDAYS(TODAY(), [End Date]@row) < 0, "Red", IF(NETWORKDAYS(TODAY(), [Start Date]@row) <= 6, IF(Status@row = "Not Started", "Yellow", "Green")))))


Goal: Health RYGB balls.

-red, end date past due in any status

-yellow, start date <=6days of today, status not started

-blue - status is complete

-green if all is good.


Issue: the ball will not turn Green unless within 6 days of today in a status of in progress.

I've tried to reorder the criteria, but then cannot get the formula to work at all.

Any help welcome!



Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee
    edited 02/02/22 Answer ✓

    Hi @Angela.R

    This is due to how your "Yellow" statement is structured. Instead of two IF statements, it looks like you actually want an AND statement in there.

    For example, instead of:

    IF(NETWORKDAYS(TODAY(), [Start Date]@row) <= 6, IF(Status@row = "Not Started", "Yellow",

    Try:

    IF(AND(NETWORKDAYS(TODAY(), [Start Date]@row) <= 6, Status@row = "Not Started"), "Yellow",

    Full Formula:

    =IF(ISBLANK([End Date]@row), "", IF(Status@row = "Complete", "Blue", IF(NETWORKDAYS(TODAY(), [End Date]@row) < 0, "Red", IF(AND(NETWORKDAYS(TODAY(), [Start Date]@row) <= 6, Status@row = "Not Started"), "Yellow", "Green"))))

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Answers

  • Genevieve P.
    Genevieve P. Employee
    edited 02/02/22 Answer ✓

    Hi @Angela.R

    This is due to how your "Yellow" statement is structured. Instead of two IF statements, it looks like you actually want an AND statement in there.

    For example, instead of:

    IF(NETWORKDAYS(TODAY(), [Start Date]@row) <= 6, IF(Status@row = "Not Started", "Yellow",

    Try:

    IF(AND(NETWORKDAYS(TODAY(), [Start Date]@row) <= 6, Status@row = "Not Started"), "Yellow",

    Full Formula:

    =IF(ISBLANK([End Date]@row), "", IF(Status@row = "Complete", "Blue", IF(NETWORKDAYS(TODAY(), [End Date]@row) < 0, "Red", IF(AND(NETWORKDAYS(TODAY(), [Start Date]@row) <= 6, Status@row = "Not Started"), "Yellow", "Green"))))

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!