IF statement to update RAG status
Hi there,
I would like to update my RAG Status icons (Green, Red, Yellow, and Grey balls) using a formula based on Due Date, Revised Due Date, and Submission Date inputs. My formula is as follows, and currently generates a #INVALID error message:
=IF([Submission Date]1 > 0, "Green", IF([Revised Due Date]1 - [Due Date]1 > 0, "Gray", IF([Due Date]1 < TODAY(), "Red", IF([Due Date]1 = TODAY(), "Yellow", IF([Due Date]1 > TODAY(), "Not Started")))))
Any ideas on what I am doing wrong? My Due Date, Revised Due Date, and Submission Date columns are all in Date format. My statement also seems to work fine when I remove the first Submission Date part of the formula.
What I want it to do is go green if there is a submission date (regardless of whether the item was late or not), go gray if there is a revised due date and no submission date (regardless of whether the item was late or not), go yellow if it is due today and no revised due date or submission date has been populated, go red if the due date was in the past and no revised due date or submission date has been populated, and say "Not Started" if the due date is in the future and a revised / submission date has not been populated.
Thanks!
Comments
-
L_123 ✭✭✭✭✭✭
you can't compare a number to a day without separating the date (In your current formula you do this in the first 2 if statements). Here is an updated version of your formula that works, though you will need to edit it to make it do what you want
=IF(ISDATE([Submission Date]1), "Green", IF(NETDAYS([Revised Due Date]1, [Due Date]1) > 0, "Gray", IF([Due Date]1 < TODAY(), "Red", IF([Due Date]1 = TODAY(), "Yellow", IF([Due Date]1 > TODAY(), "Not Started")))))
-
thanks Luke_TK. It is going green now when I have a submission date, but is giving me a #invalid error message rather than defaulting to the false part of my first IF statement... How do you get the formula to default to the rest of the IF statement?
-
L_123 ✭✭✭✭✭✭
I just tested it and the if(isdate() defintely pushes to false on a blank cell. I think it is probably a typo or error in the next if statement that is being caught and pushing the error. can you post your updated formula?
-
啊明白了排序!谢谢你的帮助!
Categories
I tested what you report in my own environment and the behavior aligns with what is expected, I can see allocation in all projects that I've been added and that have Legacy Resource Management<\/a> enabled. If I duplicate a project and keep the same name, then I see the project also duplicated in the Resource View as expected. This said, it looks like this might be the case in your own environment. Please search for any of the duplicate project names either with the \"Browse\" button in the Navigation bar <\/a>or by using the search bar<\/a> in the top right corner to review how many of your projects are named the same.<\/p> If you find that there aren't any duplicate project sheet but you still see the same behavior, I would recommend opening a ticket with our Support team via this form<\/a>.<\/p> I hope that this can be of help.<\/p> Cheers!<\/p> Julio<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-17 03:21:47","updateUser":{"userID":112016,"name":"Phil Wightman","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Phil%20Wightman","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/2019-11\/35\/2e\/n352e0e53bf6c86b6f26b8d2be7cf0153.JPG","dateLastActive":"2022-10-17T04:41:53+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":468,"urlcode":"resource-management","name":"Resource Management"}]},{"discussionID":96615,"type":"question","name":"Notification - If cell is blank","excerpt":"So I'm wanting to set-up a notification to go out to the contact anytime the \"Completed By\" column in that row is blank. Would like a daily notification sent out until that cell has a value. When setting up the automation, it looks like a change of some type has to occur in the row in order for the trigger. Maybe I'm in…","categoryID":321,"dateInserted":"2022-10-14T19:21:03+00:00","dateUpdated":null,"dateLastComment":"2022-10-15T07:46:44+00:00","insertUserID":127268,"insertUser":{"userID":127268,"name":"Reggie Andaya","url":"https:\/\/community.smartsheet.com\/profile\/Reggie%20Andaya","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-16T23:28:25+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/pictures\/f4\/d4\/nf4d40b307033e1c1fd4cfe2ab2c10220.jpg","dateLastActive":"2022-10-16T12:37:58+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":20,"score":null,"hot":3331597067,"url":"https:\/\/community.smartsheet.com\/discussion\/96615\/notification-if-cell-is-blank","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/96615\/notification-if-cell-is-blank","format":"Rich","lastPost":{"discussionID":96615,"commentID":347293,"name":"Re: Notification - If cell is blank","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/347293#Comment_347293","dateInserted":"2022-10-15T07:46:44+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/drupal_profile\/files\/pictures\/f4\/d4\/nf4d40b307033e1c1fd4cfe2ab2c10220.jpg","dateLastActive":"2022-10-16T12:37:58+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-15T02:08:51+00:00","dateAnswered":"2022-10-14T22:10:21+00:00","acceptedAnswers":[{"commentID":347282,"body":" Hi @Reggie Andaya<\/a> <\/p> I hope you're well and safe!<\/p> You can use the Trigger: When a date is reached and set up a custom rule.<\/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","log":{"dateUpdated":"2022-10-15 02:08:51","updateUser":{"userID":127268,"name":"Reggie Andaya","url":"https:\/\/community.smartsheet.com\/profile\/Reggie%20Andaya","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-16T23:28: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":96609,"type":"question","name":"How can I convert a Dynamic View to read only","excerpt":"Hi, How can I convert a Dynamic View to read only so that users can no longer make any entries in the details panel?","categoryID":343,"dateInserted":"2022-10-14T17:54:20+00:00","dateUpdated":null,"dateLastComment":"2022-10-14T19:34:27+00:00","insertUserID":140693,"insertUser":{"userID":140693,"name":"User251","title":"","url":"https:\/\/community.smartsheet.com\/profile\/User251","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-14T19:48:45+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":140693,"lastUser":{"userID":140693,"name":"User251","title":"","url":"https:\/\/community.smartsheet.com\/profile\/User251","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-14T19:48:45+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":24,"score":null,"hot":3331547327,"url":"https:\/\/community.smartsheet.com\/discussion\/96609\/how-can-i-convert-a-dynamic-view-to-read-only","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/96609\/how-can-i-convert-a-dynamic-view-to-read-only","format":"Rich","tagIDs":[443],"lastPost":{"discussionID":96609,"commentID":347266,"name":"Re: How can I convert a Dynamic View to read only","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/347266#Comment_347266","dateInserted":"2022-10-14T19:34:27+00:00","insertUserID":140693,"insertUser":{"userID":140693,"name":"User251","title":"","url":"https:\/\/community.smartsheet.com\/profile\/User251","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-14T19:48:45+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":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2022-10-14T19:35:56+00:00","dateAnswered":"2022-10-14T18:47:51+00:00","acceptedAnswers":[{"commentID":347258,"body":" Have you tried removing all fields from the details panel or setting them all as read only?<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question","log":{"dateUpdated":"2022-10-14 19:35:56","updateUser":{"userID":140693,"name":"User251","title":"","url":"https:\/\/community.smartsheet.com\/profile\/User251","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2022-10-14T19:48:45+00:00","banned":0,"punished":0,"private":false,"label":"✭"}}},"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":443,"urlcode":"dynamic-view","name":"Dynamic View"}]}],"title":"Trending in Using Smartsheet","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/Z99BEC18F57B\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\n
