Need to tweak Health Indicator formula based on "status date"


I need to tweak the following formula to use a [Status Date] instead of "Today". We publish status reports each Wednesday as of Friday of the previous week. I have added a Status Date column that I will update each week to reflect the status date.

Current formula;

=IF([% Complete]@row = 1, "Blue", IF([Planned Duration]@row = 0, IF([Planned End Date]@row <= TODAY(7), IF([% Complete]@row < 0.8, "Red", "Yellow"), "Green"), IF([Planned Start Date]@row > TODAY(), "Green", IF([Planned End Date]@row < TODAY(), "Red", IF([% Complete]@row >= NETWORKDAYS([Planned Start Date]@row, TODAY()) / [Planned Duration]@row, "Green", "Yellow")))))

For zero duration tasks (milestones)

 If % Complete = 100, return Blue

 If the Planned End Date is at least seven days away from Status Date, return Green

 Otherwise, if % Complete is < 80, return Red

 If % Complete is between 80 and 99, return Yellow

For non-zero duration tasks

 If % Complete = 100, return Blue

 If it’s not scheduled to start yet (Planned Start Date is less than Status Date), return Green

 If it’s past the Planned End date (is less than Status Date)and % Complete <100, return Red

 If % complete >= the prorated expected completion, return Green

 Otherwise, return Yellow.

Best Answers


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!