任务状态

tdbozarth
tdbozarth ✭
在内容基本知识

我正在评估Smartsheet与MS项目。在MS项目中，我有一个自定义字段，它自动告诉我任务状态。In progress and on track意味着开始日期是今天或更早，结束日期还没有过去，完成百分比与运行时间成正比。进度滞后是相同的，但是% complete小于运行时间。过期日期是完成日期已过，而不是100%完成。完成就是100%完成。接下来的两周和两周之后的是其他状态。

smartsheet也能做到这一点吗?我不想要一个纯手工的状态。

答案

  • tdbozarth
    tdbozarth ✭

    这是我在MS Project中的公式

    image.png
    image.png

    IIf((计划时间)= 0,(IIf([%完成]= 100 5 IIf([计划完成]<现在(),4,IIf(现在(计划完成)>()和[计划开始]<现在()+ 14,6,1))))、国际金融([%完成]= 100 5 IIf([%完成]> = 100 * (Abs (ProjDateDiff(计划开始,现在())/ ProjDateDiff([计划开始],[计划完成]))),2,IIf(((计划开始)>现在()和[计划开始]<现在()+ 14)6 (IIf(现在(计划完成)>()、国际金融([计划开始]>现在(),1、3)、4))))))

  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    是的。它看起来是这样的……


    =IF([% Complete]@row = 1， "Complete"， IF(OR([预定完成]@row < TODAY()，([预定完成]@row - TODAY()) /([预定完成]@row - TODAY()) < [% Complete]@row)， "落后于计划"，IF([预定开始]@row> TODAY(14)， "在两周内开始"，IF([预定开始]@row> TODAY()， "在两周内开始"，"在进行中/在进行中"))))

    thinkspi.com

  • sravya.gupta127326
    sravya.gupta127326 ✭✭✭✭✭✭

    你好,

    我们还使用一个公式来计算状态。然而，我们现在正在寻找使用自动化(更改单元)代替公式。这将允许用户在卡视图中更改状态，如果他们愿意，而不是%更改。

    供参考，公式如下:

    =如果((电子邮件保护)= true， "Stopped"， IF((电子邮件保护)= ""， "Not Started"， IF([% Complete]@row = 1， "Complete"， IF([结束日期]@row < TODAY()， "过期"，IF([At Risk]@row = 1， "At Risk"， IF([开始日期]@row > TODAY()， "Not Started"， IF([% Complete]@row = 0， "Not Started"， "On Track")))))))

    谢谢,

    Sravya

  • tdbozarth
    tdbozarth ✭
    Yes. It would look something like this...<\/p>

    =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"))))<\/p>","bodyRaw":"[{\"insert\":\"Yes. It would look something like this...\\n\\n=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\\\"))))\\n\"}]","format":"rich","dateInserted":"2022-03-31T15:39:44+00:00","insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2022-09-01T20:14:43+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/324683#Comment_324683","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/324683#Comment_324683

    @Paul新来的谢谢你的好意。当我放入一个列时，我得到#UNPARSEABLE。

  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    再次检查列名是否正确。我使用了你在第一个样本公式中列出的东西，但如果它们与表格中实际的内容不匹配，那么你就会得到错误。

    thinkspi.com

  • tdbozarth
    tdbozarth ✭

    @Paul新来的这些不是列名，它们是MS Project中的函数。因为我不知道智能表中类似的功能，所以我问是否可以这样做。例如，MS Project知道一个任务应该完成70%，所以如果它低于70%，我就可以知道它落后于计划。它还将计划开始和计划完成日期与实际开始和实际完成日期分开。

  • 保罗新来的
    保罗新来的 ✭✭✭✭✭✭

    您需要创建列来存放这些不同的部分(Start Date, Finish Date， % Complete等)，然后在公式中引用它们。

    thinkspi.com

类别

Hi @Kaleb Riley<\/a><\/p>

I hope you're well and safe!<\/p>

Have you checked the zoom level?<\/p>

You could also test log out\/in and test another browser.<\/p>

Did that work\/help? <\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic week!<\/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","log":{"dateUpdated":"2022-09-02 11:57:16","updateUser":{"userID":143030,"name":"Kaleb Riley","url":"https:\/\/community.smartsheet.com\/profile\/Kaleb%20Riley","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-02T12:05:03+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":219,"urlcode":"Sheets","name":"Sheets"}]},{"discussionID":94966,"type":"question","name":"If I deactivate a licensed user, does their license return to the pool of available licenses?","excerpt":"I am the System Admin on an Enterprise plan. If I deactivate a licensed user, does their license return to the pool of available licenses? It still shows them as licensed in User Management, and I can't make any changes to the user without reactivating them.","categoryID":321,"dateInserted":"2022-09-01T15:41:39+00:00","dateUpdated":null,"dateLastComment":"2022-09-01T16:55:21+00:00","insertUserID":86866,"insertUser":{"userID":86866,"name":"Jeff Reisman","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Jeff%20Reisman","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/3V8W2AM105QE\/nIDXI4I7VDS7W.JPG","dateLastActive":"2022-09-01T16:53:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":null,"lastUserID":86866,"lastUser":{"userID":86866,"name":"Jeff Reisman","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Jeff%20Reisman","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/3V8W2AM105QE\/nIDXI4I7VDS7W.JPG","dateLastActive":"2022-09-01T16:53:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":41,"score":null,"hot":3324099420,"url":"https:\/\/community.smartsheet.com\/discussion\/94966\/if-i-deactivate-a-licensed-user-does-their-license-return-to-the-pool-of-available-licenses","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/94966\/if-i-deactivate-a-licensed-user-does-their-license-return-to-the-pool-of-available-licenses","format":"Rich","lastPost":{"discussionID":94966,"commentID":342070,"name":"Re: If I deactivate a licensed user, does their license return to the pool of available licenses?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/342070#Comment_342070","dateInserted":"2022-09-01T16:55:21+00:00","insertUserID":86866,"insertUser":{"userID":86866,"name":"Jeff Reisman","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Jeff%20Reisman","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/3V8W2AM105QE\/nIDXI4I7VDS7W.JPG","dateLastActive":"2022-09-01T16:53:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Using Smartsheet","url":"https:\/\/community.smartsheet.com\/categories\/using-smartsheet"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-09-01T16:53:29+00:00","dateAnswered":"2022-09-01T16:06:31+00:00","acceptedAnswers":[{"commentID":342056,"body":"

Yes, the user you deactivate will no longer have access to the account, but you will not have to transfer ownership of their items and their license will become available again. You have to be careful though, if you reassign that license that user comes back needing one then you will have to make an executive decision on who gets one and who doesn't.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-09-01 16:53:29","updateUser":{"userID":86866,"name":"Jeff Reisman","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Jeff%20Reisman","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/3V8W2AM105QE\/nIDXI4I7VDS7W.JPG","dateLastActive":"2022-09-01T16:53:04+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"}}},"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":[]},{"discussionID":94961,"type":"question","name":"How can I use Smartsheet to create Surveys?","excerpt":"I need to get some feedback from a customer group and I want to use Smartsheet to conduct a survey. Has anyone done this?","categoryID":321,"dateInserted":"2022-09-01T14:50:51+00:00","dateUpdated":"2022-09-01T14:52:00+00:00","dateLastComment":"2022-09-01T16:00:37+00:00","insertUserID":151981,"insertUser":{"userID":151981,"name":"GMay","url":"https:\/\/community.smartsheet.com\/profile\/GMay","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-03T19:13:28+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":151981,"lastUserID":151981,"lastUser":{"userID":151981,"name":"GMay","url":"https:\/\/community.smartsheet.com\/profile\/GMay","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-03T19:13:28+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":24,"score":null,"hot":3324093088,"url":"https:\/\/community.smartsheet.com\/discussion\/94961\/how-can-i-use-smartsheet-to-create-surveys","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/94961\/how-can-i-use-smartsheet-to-create-surveys","format":"Rich","lastPost":{"discussionID":94961,"commentID":342055,"name":"Re: How can I use Smartsheet to create Surveys?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/342055#Comment_342055","dateInserted":"2022-09-01T16:00:37+00:00","insertUserID":151981,"insertUser":{"userID":151981,"name":"GMay","url":"https:\/\/community.smartsheet.com\/profile\/GMay","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-03T19:13:28+00:00","banned":0,"punished":0,"private":false,"label":"✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Using Smartsheet","url":"https:\/\/community.smartsheet.com\/categories\/using-smartsheet"},{"name":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-09-01T15:58:15+00:00","dateAnswered":"2022-09-01T15:27:24+00:00","acceptedAnswers":[{"commentID":342048,"body":"

Hi @GMay<\/a> <\/p>

I hope you're well and safe!<\/p>

You could use a form with the questions or the Update Request feature.<\/p>

Would any of those options work\/help? <\/p>

I hope that helps!<\/p>

Be safe, and have a fantastic week!<\/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","log":{"dateUpdated":"2022-09-01 15:58:15","updateUser":{"userID":151981,"name":"GMay","url":"https:\/\/community.smartsheet.com\/profile\/GMay","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-09-03T19:13:28+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":[]}],"title":"Trending in Using Smartsheet","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">