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))))))
-
Paul Newcome ✭✭✭✭✭✭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"))))
-
sravya.gupta127326 ✭✭✭✭✭
Hi,
我们还用一个公式来计算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 NewcomeThank you for this = very kind. When I put into a column I am getting #UNPARSEABLE.
-
Paul Newcome ✭✭✭✭✭✭
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 NewcomeThose 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.
-
Paul Newcome ✭✭✭✭✭✭
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.
Categories
Hi,<\/p>
I hope you're well and safe!<\/p>
Unfortunately, as far as I know, it's not possible now (if you mean that the Query should be included in the URL to Dashboard, it's only possible in the URL to the form), but it's an excellent idea!<\/p>
Please submit this as a Product Feedback or Idea <\/strong>(If it hasn't been added already)<\/em><\/strong> when you have a moment.<\/strong><\/a><\/p> How would you like to use it? What's the use case?<\/p> I hope that helps!<\/p> Be safe, and have a fantastic weekend!<\/p> Best,<\/p> Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":321,"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":204,"urlcode":"Forms","name":"Forms"},{"tagID":254,"urlcode":"Formulas","name":"Formulas"},{"tagID":292,"urlcode":"Dashboards","name":"Dashboards"},{"tagID":530,"urlcode":"url","name":"URL"}]},{"discussionID":108886,"type":"question","name":"Why does a user show an email address?","excerpt":"When I'm adding users to Assigned, it usually shows the First Name Last Name, but sometimes it just shows the email address. How do I fix this? See screenshot example.","snippet":"When I'm adding users to Assigned, it usually shows the First Name Last Name, but sometimes it just shows the email address. How do I fix this? See screenshot example.","categoryID":321,"dateInserted":"2023-08-12T18:59:03+00:00","dateUpdated":null,"dateLastComment":"2023-08-12T20:33:33+00:00","insertUserID":128842,"insertUser":{"userID":128842,"name":"Karen Bruer","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Karen%20Bruer","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!5MPAuTrWZX4!elHDp9qhGX4!HMSKFvshvfI","dateLastActive":"2023-08-13T15:19:32+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"updateUserID":null,"lastUserID":161632,"lastUser":{"userID":161632,"name":"SteyJ","title":"Jacob Stey","url":"https:\/\/community.smartsheet.com\/profile\/SteyJ","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!5Zjq-V_rc4o!zmkrczO71bo!6kwXWfi9FUH","dateLastActive":"2023-08-14T16:33:47+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":29,"score":null,"hot":3383739756,"url":"https:\/\/community.smartsheet.com\/discussion\/108886\/why-does-a-user-show-an-email-address","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108886\/why-does-a-user-show-an-email-address","format":"Rich","lastPost":{"discussionID":108886,"commentID":390375,"name":"Re: Why does a user show an email address?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390375#Comment_390375","dateInserted":"2023-08-12T20:33:33+00:00","insertUserID":161632,"insertUser":{"userID":161632,"name":"SteyJ","title":"Jacob Stey","url":"https:\/\/community.smartsheet.com\/profile\/SteyJ","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!5Zjq-V_rc4o!zmkrczO71bo!6kwXWfi9FUH","dateLastActive":"2023-08-14T16:33:47+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/I85D28MOQEGC\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-13T15:20:11+00:00","dateAnswered":"2023-08-12T20:33:33+00:00","acceptedAnswers":[{"commentID":390375,"body":"
