IF语句更新RAG状态

RobynTocco
RobynTocco
编辑12/09/19 在内容基本知识

你好,

我想更新我的RAG状态图标(绿色，红色，黄色和灰色球)使用基于到期日，修订到期日和提交日期输入的公式。我的公式如下，目前产生一个#INVALID错误消息:

=IF([提交日期]1 > 0，"绿色"，IF([修订截止日期]1 -[截止日期]1 > 0，"灰色"，IF([截止日期]1 <今日()，"红色"，IF([截止日期]1 =今日()，"黄色"，IF([截止日期]1 >今日()，"未开始")))))

对我做错了什么有什么想法吗?我的截止日期，修改截止日期，和提交日期列都是日期格式。当我删除公式的第一个提交日期部分时，我的声明似乎也工作得很好。

我想要做的是走绿色如果有提交日期(无论是否项目迟到),灰色的如果有一个修改后的到期日期和提交日期(无论是否项目迟到),黄色如果今天到期,没有修改到期日期或提交日期填充,如果到期日期在过去,没有修改到期日期或提交日期填充,如果截止日期在未来，并且尚未填写修改/提交日期，则显示“未开始”。

谢谢!

· 在Facebook上分享 在Twitter上分享

评论

  • L_123
    L_123 ✭✭✭✭✭✭
    编辑05/21/18

    如果不把日期分开，你就不能把一个数字和一天进行比较(在你当前的公式中，你在前两个if语句中这样做)。这里是你的公式的更新版本，虽然你需要编辑它，使它做你想要的

    =IF(ISDATE([提交日期]1)，"绿色"，IF(NETDAYS([修订截止日期]1，[截止日期]1)> 0，"灰色"，IF([截止日期]1 <今日()，"红色"，IF([截止日期]1 =今日()，"黄色"，IF([截止日期]1 >今日()，"未开始")))))

    · 在Facebook上分享 在Twitter上分享
  • RobynTocco
    RobynTocco

    谢谢Luke_TK。当我有一个提交日期时，它现在是绿色的，但是给我一个#invalid错误信息，而不是默认的第一个IF语句的false部分…如何使公式默认为IF语句的其余部分?

    · 在Facebook上分享 在Twitter上分享
  • L_123
    L_123 ✭✭✭✭✭✭

    我刚刚测试了它，并且if(isdate()在空白单元格上绝对推送为false。我认为这可能是下一个if语句中的错字或错误，它被捕获并推送错误。你能更新一下你的配方吗?

    · 在Facebook上分享 在Twitter上分享
  • RobynTocco
    RobynTocco

    啊，解决了!谢谢你的帮助!

    · 在Facebook上分享 在Twitter上分享

类别

Boy. I am not sure that is possible. You could reference the cell, but there is no way to link to a cell in a sheet.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-19 20:20:00","updateUser":{"userID":148706,"name":"Bradhickswa","url":"https:\/\/community.smartsheet.com\/profile\/Bradhickswa","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!AuHRX9yKuf0!3hlSP6CUF7s!8dWYTxFWd_v","dateLastActive":"2022-10-19T20:19:51+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"},{"tagID":472,"urlcode":"cell-linking","name":"Cell linking"}]},{"discussionID":96757,"type":"question","name":"How can a contact field by aggregated to show a count?","excerpt":"I have a list of projects and each project has an individual as a contact. An individual could be listed for multiple projects. I'd like to chart to reflect how many projects each individual is responsible for. The trouble I'm encountering is that the report doesn't allow me to summarize on the contact list field and the…","categoryID":321,"dateInserted":"2022-10-18T21:49:13+00:00","dateUpdated":"2022-10-19T11:07:15+00:00","dateLastComment":"2022-10-19T19:18:05+00:00","insertUserID":152808,"insertUser":{"userID":152808,"name":"Carol C","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Carol%20C","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!HDtrnzPHiTs!3ZC0_406k3Q!wP1SpC5Lr5S","dateLastActive":"2022-10-19T15:53:25+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"updateUserID":91566,"lastUserID":45516,"lastUser":{"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-10-19T19:52:15+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":22,"score":null,"hot":3332338638,"url":"https:\/\/community.smartsheet.com\/discussion\/96757\/how-can-a-contact-field-by-aggregated-to-show-a-count","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/96757\/how-can-a-contact-field-by-aggregated-to-show-a-count","format":"Rich","tagIDs":[247,265,292],"lastPost":{"discussionID":96757,"commentID":347828,"name":"Re: How can a contact field by aggregated to show a count?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/347828#Comment_347828","dateInserted":"2022-10-19T19:18:05+00:00","insertUserID":45516,"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-10-19T19:52:15+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-10-19T15:55:37+00:00","dateAnswered":"2022-10-19T13:10:54+00:00","acceptedAnswers":[{"commentID":347759,"body":"

To summarize on a field you have to group by it first. You can group on a contact field so long as it is not set to allow multiple entries per cell.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-19 15:55:37","updateUser":{"userID":152808,"name":"Carol C","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Carol%20C","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!HDtrnzPHiTs!3ZC0_406k3Q!wP1SpC5Lr5S","dateLastActive":"2022-10-19T15:53: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":[{"tagID":247,"urlcode":"Contacts","name":"Contacts"},{"tagID":265,"urlcode":"Reports","name":"Reports"},{"tagID":292,"urlcode":"Dashboards","name":"Dashboards"}]},{"discussionID":96756,"type":"question","name":"Returning to a Form for a Specific Row","excerpt":"Can I return to a form for a specific row? I know I can use edit to change or update the row in a similar format, but it does not allow me to return to the full form with added instructions and notes.","categoryID":321,"dateInserted":"2022-10-18T21:32:17+00:00","dateUpdated":"2022-10-19T11:07:17+00:00","dateLastComment":"2022-10-19T17:57:51+00:00","insertUserID":149904,"insertUser":{"userID":149904,"name":"Curious Georgia","url":"https:\/\/community.smartsheet.com\/profile\/Curious%20Georgia","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!HqjVH893nlY!ASdUgMdi0Do!v9-15DEX4gR","dateLastActive":"2022-10-19T21:47:08+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":91566,"lastUserID":120260,"lastUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2022-10-19T22:39:37+00:00","banned":0,"punished":0,"private":false,"label":"Overachievers"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":20,"score":null,"hot":3332331608,"url":"https:\/\/community.smartsheet.com\/discussion\/96756\/returning-to-a-form-for-a-specific-row","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/96756\/returning-to-a-form-for-a-specific-row","format":"Rich","lastPost":{"discussionID":96756,"commentID":347812,"name":"Re: Returning to a Form for a Specific Row","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/347812#Comment_347812","dateInserted":"2022-10-19T17:57:51+00:00","insertUserID":120260,"insertUser":{"userID":120260,"name":"David Tutwiler","title":"","url":"https:\/\/community.smartsheet.com\/profile\/David%20Tutwiler","photoUrl":"https:\/\/lh3.googleusercontent.com\/a-\/AOh14GiIpuCpV7Zvo9nduJmLt8ULNYObTVazzemdnIiy6w=s96-c","dateLastActive":"2022-10-19T22:39:37+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":"Smartsheet Basics","url":"https:\/\/community.smartsheet.com\/categories\/smartsheet-basics%2B"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-10-19T21:47:47+00:00","dateAnswered":"2022-10-19T17:57:51+00:00","acceptedAnswers":[{"commentID":347812,"body":"

I don't believe this is possible in current state. As you said, you can do Update Requests, but Forms are for inputs into the sheet and won't auto-fill with row data.<\/p>

I would recommend that you put this in as a possible enhancement request. You can do that from directly within the community now at the following page:<\/p>

\n \n https:\/\/community.smartsheet.com\/categories\/smartsheet-product-feedback-%26-ideas\n <\/a>\n<\/div>


<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-19 21:47:47","updateUser":{"userID":149904,"name":"Curious Georgia","url":"https:\/\/community.smartsheet.com\/profile\/Curious%20Georgia","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!HqjVH893nlY!ASdUgMdi0Do!v9-15DEX4gR","dateLastActive":"2022-10-19T21:47:08+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":[]}">