Task Status
I am evaluating Smartsheet against MS Project. In MS Project I have a custom field that tells me task status automatically. In progress and on track means the start date is today or earlier, the end date has not passed and the % complete is proportional to elapsed time. Behind schedule is same but % complete is less than elapsed time. Past Due is finished date passed, not 100% complete. Complete is 100% complete. Coming up in next 2 weeks and coming up beyond two weeks are other statuses.
Can smartsheet do this somehow as well? I do not want a purely manual status.
Answers
-
This is my formula in MS Project
IIf([Scheduled Duration] = 0, (IIf([% Complete] = 100, 5, IIf([Scheduled Finish] < Now(), 4, IIf([Scheduled Finish] > Now() And [Scheduled Start] < Now() + 14, 6, 1)))), IIf([% Complete] = 100, 5, IIf([% Complete] >= 100 * (Abs(ProjDateDiff([Scheduled Start], Now()) / ProjDateDiff([Scheduled Start], [Scheduled Finish]))), 2, IIf(([Scheduled Start] > Now() And [Scheduled Start] < Now() + 14), 6, (IIf([Scheduled Finish] > Now(), IIf([Scheduled Start] > Now(), 1, 3), 4))))))
-
Yes. It would look something like this...
=IF([% Complete]@row = 1, "Complete", IF(OR([Scheduled Finish]@row < TODAY(), ([Scheduled Finish]@row - TODAY()) / ([Scheduled Finish]@row - [Scheduled Start]@row)< [% Complete]@row), "Behind Schedule", IF([Scheduled Start]@row> TODAY(14), "Starts in more than two weeks", IF([Scheduled Start]@row> TODAY(), "Starts within two weeks", "In Progress/On Track"))))
-
Hi,
We also use a formula to calculate the status. However, we are now looking to use Automation (Change Cell) instead of the formula. This will allow users to change status in card view if they would like instead of % change.
For reference, here is the formula:
=IF(Stopped@row = true, "Stopped", IF(Duration@row = "", "Not Started", IF([% Complete]@row = 1, "Complete", IF([End Date]@row < TODAY(), "Overdue", IF([At Risk]@row = 1, "At Risk", IF([Start Date]@row > TODAY(), "Not Started", IF([% Complete]@row = 0, "Not Started", "On Track")))))))
Thanks,
Sravya
-
@Paul Newcome Thank you for this = very kind. When I put into a column I am getting #UNPARSEABLE.
-
Double check that the column names are correct. I used what you had listed in your first sample formula, but if those do not match what is actually in the sheet then you will get that error.
-
@Paul Newcome Those are not column names they are functions within MS Project. And it is because I do not know that similar functions in smart sheets that I am asking if this can be done or not. For example, MS Project knows that a task should be 70% complete for example so if it is less than that I can tell it is behind schedule. It also keeps scheduled start and scheduled finish dates separately from actual start and actual finish, etc.
-
You will need to create columns that house each of these different pieces (Start Date, Finish Date, % Complete, etc.) then reference them in the formula.
