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

Stephanie Zeoli

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")))))

Paul Newcome

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

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

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

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! :)

Paul Newcome

Happy to help. 👍️