A way to automate status/ progress based on % complete, duration and baseline end date

Sparky84

ssheetexamp.PNG

Sorry if this has been solved previously, and for the long message!

I'd like to create an auto status column that is based on % complete, duration and baseline end date for each task.

I've added 3 helper columns (image attached), but I'm stuck with the formula for the status:

-'Days Complete %' is based on the duration (minus the variance, to give the baseline duration) multiplied by the % complete. This gives the amount of days worked based on the entered percentage

-'Days Remaining %' is based on the baseline duration calculation mentioned above, minus the Days Complete %. This gives the amount of days of work remaining

-'Days Remaining From Baseline Finish To Today' is what it says on the tin. The number of days left between now and the baseline end date

The logic of what I'm trying to achieve here, if you look at row 163, the task is marked as 60% complete, 45 days of work has been completed, 30 required days are remaining, and there are 47 days remaining from today and the baseline end date. Therefore, this task is "Green" and "On Track".

Row 173, the task is marked 18% complete, there are 68 required days remaining, but there are only 59 days remaining from today to the end baseline end date. Therefore, this task is "Yellow" and 9 days "Off Track"

I'd like there to be a 2 week lag between the Yellow and Red status. So once the difference between Days Remaining From Baseline Finish To Today & Days Remaining % (the amount of work required is greater than the amount of time left) becomes greater than 2 weeks, it will go from "Off Track" to "At Risk".

Many Thanks!

Sparky84

I've come up with this so far, Blue, Green & Yellow are working, but I can't get the red part to work yet.

=IF([% Complete]@row = 1, "Blue", IF([Days Remaining From Baseline Finish To Today]@row >= [Days Remaining %]@row, "Green", IF([Days Remaining From Baseline Finish To Today]@row > [Days Remaining %]@row + 14, "Red", IF([Days Remaining From Baseline Finish To Today]@row < [Days Remaining %]@row, "Yellow"))))