RYGB health changes on date and status

Angela.R
Angela.R ✭✭✭

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


image.png


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

    Need more information? πŸ‘€ | Help and Learning Center

    こんにけは (Konnichiwa), Hallo, Hola, Bonjour, OlΓ‘, Ciao!πŸ‘‹ | Global Discussions

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

    Need more information? πŸ‘€ | Help and Learning Center

    こんにけは (Konnichiwa), Hallo, Hola, Bonjour, OlΓ‘, Ciao!πŸ‘‹ | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!