任务状态

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

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

答案

  • 这是我在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

  • 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-12-05T19:12:04+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

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

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

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

    thinkspi.com

Hey @SkiPatrolScott<\/a> <\/p>

Yes, you can use an existing summary field in another summary field within the same sheet. You can either click into the formula field of the existing formula to add to new formula, or type out the field name. Summary fields use a # in place of a row indicator. Using your example from above:<\/p>

=new formula =[SS-Result1]#<\/strong>-[SS-Result2]#<\/strong><\/p>

If this doesn't make sense, help me understand where I lost you and I will try to explain it better.<\/p>

Kelly<\/p>"},{"commentID":353136,"body":"

Thanks @Kelly Moore<\/a> , I did have to add a ( around the equation ) but it did work.<\/p>

=sum([summary field1]#-[summary field2]) the correct answer was produced.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-12-05 14:48:17","updateUser":{"userID":154303,"name":"SkiPatrolScott","url":"https:\/\/community.smartsheet.com\/profile\/SkiPatrolScott","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-12-05T17:39:25+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":98381,"type":"question","name":"Smartsheet Form Logo Question","excerpt":"Hello Smartsheet Community, I have a question that is probably obvious that I am overlooking it. I am creating and I am incorporating a logo into the form. I am able to incorporate the logo, but it's small when you open the form. Is there a way to make the logo bigger? Or, elongate the image so it goes across the top of…","categoryID":321,"dateInserted":"2022-12-02T20:32:19+00:00","dateUpdated":null,"dateLastComment":"2022-12-03T18:37:12+00:00","insertUserID":147643,"insertUser":{"userID":147643,"name":"Anthony DAmbrosio","url":"https:\/\/community.smartsheet.com\/profile\/Anthony%20DAmbrosio","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-12-05T17:14:36+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":112221,"lastUser":{"userID":112221,"name":"Kelly Moore","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kelly%20Moore","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jnPa4zng1Pw!n1lbH6Wxgbo!UbxYNkVsKJw","dateLastActive":"2022-12-06T01:39:56+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":22,"score":null,"hot":3340106371,"url":"https:\/\/community.smartsheet.com\/discussion\/98381\/smartsheet-form-logo-question","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/98381\/smartsheet-form-logo-question","format":"Rich","lastPost":{"discussionID":98381,"commentID":353078,"name":"Re: Smartsheet Form Logo Question","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/353078#Comment_353078","dateInserted":"2022-12-03T18:37:12+00:00","insertUserID":112221,"insertUser":{"userID":112221,"name":"Kelly Moore","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Kelly%20Moore","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!jnPa4zng1Pw!n1lbH6Wxgbo!UbxYNkVsKJw","dateLastActive":"2022-12-06T01:39:56+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/868U3BF057NI\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2022-12-05T17:14:43+00:00","dateAnswered":"2022-12-03T18:37:12+00:00","acceptedAnswers":[{"commentID":353078,"body":"

Hello @Anthony DAmbrosio<\/a> <\/p>

The following reference states the dimensions for a form logo is 300pix wide x 52pix tall. <\/p>

\n \n https:\/\/help.smartsheet.com\/articles\/2478921-create-a-quality-logo-to-brand-your-smartsheet-items#toc-where-your-logo-appears-and-how-smartsheet-resizes-it\n <\/a>\n<\/div>

You may need to play with your layout, as suggested in the article, to maximize the size of the text.<\/p>

Kelly<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-12-05 17:14:43","updateUser":{"userID":147643,"name":"Anthony DAmbrosio","url":"https:\/\/community.smartsheet.com\/profile\/Anthony%20DAmbrosio","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-12-05T17:14:36+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":98386,"type":"question","name":"How do I get Data Table to recognize dates uploaded by Data Shuttle?","excerpt":"Hi, I'm trying to create a new Data Table using Data Shuttle. I have some columns with date date. It seems that, no matter how I format the dates in the source CSV, Data Table won't recognize them as dates. Data Shuttle doesn't have an option to set the date format when creating a Data Table. I wish it would. I know I can…","categoryID":343,"dateInserted":"2022-12-02T23:54:10+00:00","dateUpdated":null,"dateLastComment":"2022-12-03T00:27:58+00:00","insertUserID":140451,"insertUser":{"userID":140451,"name":"Brian_Richardson","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Brian_Richardson","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/ALm5wu3gsODavHSggzZ-xp1nf0UGuLXkegzF2uAZRanwGQ=s96-c","dateLastActive":"2022-12-05T15:47:24+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers"},"updateUserID":null,"lastUserID":140451,"lastUser":{"userID":140451,"name":"Brian_Richardson","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Brian_Richardson","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/ALm5wu3gsODavHSggzZ-xp1nf0UGuLXkegzF2uAZRanwGQ=s96-c","dateLastActive":"2022-12-05T15:47:24+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":25,"score":null,"hot":3340053128,"url":"https:\/\/community.smartsheet.com\/discussion\/98386\/how-do-i-get-data-table-to-recognize-dates-uploaded-by-data-shuttle","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/98386\/how-do-i-get-data-table-to-recognize-dates-uploaded-by-data-shuttle","format":"Rich","tagIDs":[426,474],"lastPost":{"discussionID":98386,"commentID":353068,"name":"Re: How do I get Data Table to recognize dates uploaded by Data Shuttle?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/353068#Comment_353068","dateInserted":"2022-12-03T00:27:58+00:00","insertUserID":140451,"insertUser":{"userID":140451,"name":"Brian_Richardson","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Brian_Richardson","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/ALm5wu3gsODavHSggzZ-xp1nf0UGuLXkegzF2uAZRanwGQ=s96-c","dateLastActive":"2022-12-05T15:47:24+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Using Smartsheet","url":"https:\/\/community.smartsheet.com\/categories\/using-smartsheet"},{"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-12-03T00:28:02+00:00","dateAnswered":"2022-12-03T00:27:58+00:00","acceptedAnswers":[{"commentID":353068,"body":"

Never mind, I found the solution. If you import dates in the format YYYY-MM-DDT00:00:00Z then Data Table will recognize that format as a date. I also tested syncing to a Sheet and the sheet recognizes that as a date and displays it correctly based on regional setting (mine being MM\/DD\/YY)<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-12-03 00:28:02","updateUser":{"userID":140451,"name":"Brian_Richardson","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Brian_Richardson","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/ALm5wu3gsODavHSggzZ-xp1nf0UGuLXkegzF2uAZRanwGQ=s96-c","dateLastActive":"2022-12-05T15:47:24+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers"}}},"bookmarked":false,"unread":false,"category":{"categoryID":343,"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations","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":426,"urlcode":"DataTable","name":"DataTable"},{"tagID":474,"urlcode":"data-shuttle","name":"Data Shuttle"}]}],"title":"Trending in Using Smartsheet","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">