Health status in a project plan

Options
Yaya
Yaya ✭✭
edited 10/17/22 in Formulas and Functions

Hi,

I am wondering if anybody can help me with setting up a formula that flags Red, Yellow, Green based on Status and Due Date.


If the status is not complete and the due date is in the past more than 5 working dates days, then yellow.

If the status is not complete and the due date is in the past more than 10 working days, then red.

If the status is blocked or on hold, flag it as red.

Otherwise, it displays Green.


Would very much appreciate a quick response!


Many thanks


Yaya

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Yaya

    Sure! This actually simplifies things.

    If you put the first statement, that Status = "Complete" at the front, it means we can take out the statements that say "Status is not Complete" because the formula will only move on to the next statements if the first is false.


    =IF(Status@row = "Complete", "Gray",

    IF(OR([Start Date]@row = "", [Due Date]@row = ""), "Yellow",

    IF(OR(Status@row = "Blocked", Status@row = "On Hold", NETWORKDAYS([Due Date]@row, TODAY()) >= 5), "Red",

    IF([Due Date]@row < TODAY(), "Yellow",

    "Green"))))


    Side note - I realized I had the data within the NETWORKDAYS function backwards and swapped it around. This should work better for you!

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Yaya

    I'd be happy to help you with this. I'll spell out each of the separate IF statements based on your conditions in the best order, then post the full formula at the bottom.

    IF formulas stop as soon as they've found a correct condition, so we'll start with the RED statements:

    • If the status is blocked or on hold, flag it as red.

    =IF(OR(Status@row = "Blocked", Status@row = "On Hold"), "Red"

    • If the status is not complete and the due date is in the past more than 10 working days, then red.

    IF(AND(Status@row <> "Complete", NETWORKDAYS(TODAY(), [Due Date]@row) > 10), "Red"

    • If the status is not complete and the due date is in the past between 6 - 10 working days, then yellow.

    IF(AND(Status@row <> "Complete", NETWORKDAYS(TODAY(), [Due Date]@row) > 5), "Yellow"

    • Otherwise, it displays Green. (Note that this means any task with a due date in the past up to 5 days late will show Green)

    , "Green"


    Full Formula:

    =IF(OR(Status@row = "Blocked", Status@row = "On Hold"), "Red", IF(AND(Status@row <> "Complete", NETWORKDAYS(TODAY(), [Due Date]@row) > 10), "Red", IF(AND(Status@row <> "Complete", NETWORKDAYS(TODAY(), [Due Date]@row) > 5), "Yellow", "Green")))


    Helpful Articles:

    NETWORKDAYS Function / OR Function / AND Function / IF Function / Create Efficient Formulas with @cell and @row

    If this isn't giving you the output you're looking for, it would be helpful to see a screen capture with example data. Cheers,

    Genevieve

  • Yaya
    Yaya ✭✭
    Options

    @Genevieve P

    Thanks for this. Upon reflection, can we rephrase it like the following logic like this?

    If the status is complete, Gray.

    If the status is not complete and the due date is in the past, but less than than 5 working dates days in the past , then yellow.

    If the status is not complete and the due date is more than 5 working days in the past more, then red.

    If the status is blocked or on hold, flag it as red.

    For fields, where there is no start nor due date, then mark it as yellow.

    Is this ok?


    Thanks a million!

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓
    Options

    Hi @Yaya

    Sure! This actually simplifies things.

    If you put the first statement, that Status = "Complete" at the front, it means we can take out the statements that say "Status is not Complete" because the formula will only move on to the next statements if the first is false.


    =IF(Status@row = "Complete", "Gray",

    IF(OR([Start Date]@row = "", [Due Date]@row = ""), "Yellow",

    IF(OR(Status@row = "Blocked", Status@row = "On Hold", NETWORKDAYS([Due Date]@row, TODAY()) >= 5), "Red",

    IF([Due Date]@row < TODAY(), "Yellow",

    "Green"))))


    Side note - I realized I had the data within the NETWORKDAYS function backwards and swapped it around. This should work better for you!

    Cheers,

    Genevieve

  • Yaya
    Yaya ✭✭
    Options

    Amazing!! Thanks so much!

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    No problem 🙂

  • Yaya
    Yaya ✭✭
    Options

    One final question - on the top on general information. Any way to exclude these cells being part of the formula or any other workaround you'd recommend?


  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Yaya

    When you say "these cells", do you mean any of your Parent rows? So you would like to exclude anything that does not have hierarchy above it?

    If so, we can add a statement in the beginning that checks for this and returns blank or "" if the current row has no ancestors:

    =IF(COUNT(ANCESTORS(Activity@row)) = 0, "",

    so:

    =IF(COUNT(ANCESTORS(Activity@row)) = 0, "", IF(Status@row = "Complete", "Gray", IF(OR([Start Date]@row = "", [Due Date]@row = ""), "Yellow", IF(OR(Status@row = "Blocked", Status@row = "On Hold", NETWORKDAYS([Due Date]@row, TODAY()) >= 5), "Red", IF([Due Date]@row < TODAY(), "Yellow", "Green")))))

  • Yaya
    Yaya ✭✭
    Options

    @Genevieve P. just the top two general information lines. Any suggestions?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Yaya

    I'm not sure what lines you mean, but if you only need to exclude two rows you could add in the IF statement at the beginning like before that returns blank if a certain identifier cell has specific text... such as "Note":

    =IF(CONTAINS("Note", Status@row), "", IF(Status@row = "Complete", "Gray", IF(OR([Start Date]@row = "", [Due Date]@row = ""), "Yellow", IF(OR(Status@row = "Blocked", Status@row = "On Hold", NETWORKDAYS([Due Date]@row, TODAY()) >= 5), "Red", IF([Due Date]@row < TODAY(), "Yellow", "Green")))))

    or

    =IF(CONTAINS("specific text", Activity@row), "", IF(Status@row = "Complete", "Gray", IF(OR([Start Date]@row = "", [Due Date]@row = ""), "Yellow", IF(OR(Status@row = "Blocked", Status@row = "On Hold", NETWORKDAYS([Due Date]@row, TODAY()) >= 5), "Red", IF([Due Date]@row < TODAY(), "Yellow", "Green")))))

  • Yaya
    Yaya ✭✭
    Options

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!