How can I get the status to change to "On Schedule" when the Finish date is within a week?

Options

I am needing help with a formula to generate a "On Schedule" status that will in turn turn my RGB balls green instead of showing up "At Risk" or red. Perhaps I'm confusing the referencing by my Health column formula ??? Any help is appreciated. Thanks!

My Health Column formula is:

=IF(Status@row = "Complete", "Blue", IF(Status@row = "In Progress", "Yellow", IF(Status@row = "On Schedule", "Green", IF(AND(Status@row <> "Complete", TODAY() > Finish@row), "Red"))))

My Status Column formula is:

=IF([% Complete]@row = 1, "Complete", IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row <> 1, "In Progress", IF(Finish@row = WEEKNUMBER(TODAY(), "On Schedule")))))



Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Nest IF statements work from left to right and stop on the first true value. That means that your % Complete arguments are going to trigger a true value before it has a change to evaluate the date. Try moving that particular IF to be the second on (after the "Complete" IF).

  • Stephanie Zeoli
    Options

    Ok, I gave that a try and now it is indicating I have a "Circular Reference". I also tried making it the first command and it still said "Circular Reference".

    =IF([% Complete]@row = 1, "Complete", IF(AND(Start@row < TODAY() - 5, Status@row <> "Complete"), "On Schedule", IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row <> 1, "In Progress"))))


    =IF(AND(Start@row < TODAY() - 5, Status@row <> "Complete"), "On Schedule", IF([% Complete]@row = 1, "Complete", IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row <> 1, "In Progress"))))



  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 06/25/21
    Options

    If you are putting this in the Status column, then you need to remove the reference to the Status column.


    =IF([% Complete]@row = 1, "Complete", IF(AND(Start@row < TODAY() - 5, Status@row <> "Complete"), "On Schedule", IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row <> 1, "In Progress"))))


    =IF([% Complete]@row = 1, "Complete", IF(Start@row < TODAY() - 5, "On Schedule", IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row <> 1, "In Progress"))))

  • Stephanie Zeoli
    Options

    THANK YOU! THANK YOU!

    This exact thing didn't work but the combination of everything you suggested did.

    This is the formula I got to work using both suggestions:

    =IF(AND([% Complete]@row = 0, Start@row < TODAY() - 5), "On Schedule", IF([% Complete]@row = 1, "Complete", IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row <> 1, "In Progress"))))

    You are greatly appreciated! Thank you for your time! :)

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!