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.