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

Melissa Kosiewicz

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.

Genevieve P.

Hi Melissa,

It looks like this is tweaking the formula that you created with @Paul Newcome, here, is that correct? https://community.smartsheet.com/discussion/66275/calculated-status-indicator-using-task-duration#latest

I haven't actually gone through and tested each statement, but if your formula works and you're just looking to replace each instance of TODAY with a reference to your Status Date @ row, try this:

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

If you want to reference the Status Date as one cell that isn't in every row, you can use what's called an "absolute reference" instead of saying @row. This article has more information: https://help.smartsheet.com/articles/2476816-create-cell-column-reference-formula

Paul Newcome

I agree with @Genevieve P's solution.

TODAY() becomes [Status Date]@row

and

TODAY(#) becomes [Status Date]@row + #

Genevieve P.

Hi Melissa,

It looks like this is tweaking the formula that you created with @Paul Newcome, here, is that correct? https://community.smartsheet.com/discussion/66275/calculated-status-indicator-using-task-duration#latest

I haven't actually gone through and tested each statement, but if your formula works and you're just looking to replace each instance of TODAY with a reference to your Status Date @ row, try this:

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

If you want to reference the Status Date as one cell that isn't in every row, you can use what's called an "absolute reference" instead of saying @row. This article has more information: https://help.smartsheet.com/articles/2476816-create-cell-column-reference-formula

Paul Newcome

I agree with @Genevieve P's solution.

TODAY() becomes [Status Date]@row

and

TODAY(#) becomes [Status Date]@row + #

Melissa Kosiewicz

Thanks @Paul Newcome and @Genevieve P this worked!

Genevieve P.

Great! Glad it worked for you!