Welcome to the New Smartsheet Online Community
You’ll notice that things have changed a bit. If you need help getting oriented, please take a look at the posts here in the Welcome to the Community category.
Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Automatically populate a "STATUS" column with 1 of 5 values
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