Automatically populate a "STATUS" column with 1 of 5 values

Ray Henry

I am looking to automatically populate a "STATUS" column with 1 of 5 values based on critera/conditions are outlined below. The key drivers here are the

- [% Complete]

- [Start] Date

- [F/C Finish] Date - this is a column I have set up that uses a formula to predict a Forecast Finish

 

([F/C Finish] = (Today() + (R x D)) where R=% Remaining (1-[% Complete]) and D=Duration. 

 

It would be much appreciated if you could take some time to show me how I best can achieve this. Can it be done in 1 formula? If so any guidance would be much appreciated.

 

Value 1: "Not Due to Start"

[Start] is in the FUTURE AND [%Complete] is equal to or less than 0%

 

Value 2: "Due to Start" - Task should have started but hasn't!

[Start] is in the PAST AND[%Complete] is equal to or less than 0%

 

Value 3: "On Track" - Task has started and the F/C Finish date is on time

[Start] is in the PAST AND [F/C Finish] is earlier or the same as the [Finish] date 

 

 

Value 4: "At Risk" - Task has started and the F/C Finish date is LATE

[Start] is in the PAST AND [F/C Finish] is later than the [Finish] 

 

Value 5: "Complete" - 

Task is 100% Complete

 

Any tips/help much appreciated

 

Thanks

 

Ray

 

 

 

 

 

 

 

 

John Sauber

This can be done in 1 formula, but recognize that the formula might not be very easy to read by anyone, and therefore, not very easy to update if you ever want to change the criteria. I'd recommend making columns that individually evaluate some of this criteria, such as a column named "Not Due to Start" as a flag or checkmark type with the formula =if(and([Start]1>today(), [% Complete]1<=0), 1, 0). Do this for the rest of your status types in their own columns.

 

In the "Status" column, you can do a pretty easy nested if statement, which will just look up the flag status of the other columns you just made. One of the IF statements will read very nicely, like =if([Not Due to Start]=1, "Not Due to Start")...it's pretty easy to read that if statement, and it actually reads pretty close to real English. Of course, that's just one, and you'll need to nest it for all statuses, but that's a good place to start.

 

Once you make your nested IF statment working, you can, if you so choose, combine everything into one fomula by replacing the reference to columns, with the formula contained within them. You can't blindly copy, though, because you'l bring in the "=" sign in the formula.

 

This should be enough to get you going, along with this:

https://app.smartsheet.com/b/publish?EQBCT=2b345b6e3e424c88b2368926728b06ea

JohnHinkle

John brings up a good suggestion of using flag columns to show a tasks status (RYG columns work well too). But, if you wanted to use a formula that contains all your variables, try this: 

 

 

=IF([% Complete]5 = 1, "Complete", IF(AND([Start Date]5 < TODAY(), [F/C Finish]5 > [End Date]5), "At Risk", IF(AND([Start Date]5 > TODAY(), [% Complete]5 = 0), "Not Due to Start", IF(AND([Start Date]5 < TODAY(), [% Complete]5 = 0), "Due to Start", IF(AND([Start Date]5 < TODAY(), [F/C Finish]5 <= [End Date]5), "On Track")))))