Due Date w/Multiple Conditions

Hi Community! I'm looking for some help. I've been tasked with creating a "notice tracker". The team would like the [Due Date]@row to be some what of a dynamic date.

Here is a snip of the sheet and the columns I'll be using.

image.png

The sheet is fed via a form. The form has "Response Due Date" but is not required. It will only be populated IF the notice received has a Response required due date. IF NOT then I'm looking to get the Due Date to be 30 days after [Date Created]@row. This is the formula that I had which worked great.

=IF([Response Due Date]@row = "", [Date Created]@row + 30, [Response Due Date]@row)

However, after discussions with the team. They are hoping I can provide a solution which can take this one step further... Below is my attempt at laying out the results in the due date given the conditions.

image.png

Please let me know if more info would be helpful

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the公式手册模板!
Is the column you are inserting the formula into formatted as a date column?<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","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":108786,"type":"question","name":"Can't Save Field Logic Updates in Dynamic View","excerpt":"Hi all, I've seen a few similar posts regarding this, but they were from a few years back so not sure if anything has changed. I want to add Field Logic to a field in our form. It asks the user to select a Category and to deter using Issues\/Miscellaneous, I have added a dropdown column in the sheet for the user to confirm…","snippet":"Hi all, I've seen a few similar posts regarding this, but they were from a few years back so not sure if anything has changed. I want to add Field Logic to a field in our form. It…","categoryID":322,"dateInserted":"2023-08-10T14:14:23+00:00","dateUpdated":null,"dateLastComment":"2023-08-10T16:42:12+00:00","insertUserID":158227,"insertUser":{"userID":158227,"name":"BristolCVN","url":"https:\/\/community.smartsheet.com\/profile\/BristolCVN","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-10T15:49:46+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-10T19:35:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":19,"score":null,"hot":3383364395,"url":"https:\/\/community.smartsheet.com\/discussion\/108786\/cant-save-field-logic-updates-in-dynamic-view","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108786\/cant-save-field-logic-updates-in-dynamic-view","format":"Rich","tagIDs":[443],"lastPost":{"discussionID":108786,"commentID":390063,"name":"Re: Can't Save Field Logic Updates in Dynamic View","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390063#Comment_390063","dateInserted":"2023-08-10T16:42:12+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-10T19:35:44+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":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/8C0H47JEM9MT\/fieldlogic.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"fieldlogic.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-10T15:49:43+00:00","dateAnswered":"2023-08-10T14:51:23+00:00","acceptedAnswers":[{"commentID":390035,"body":"

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

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

You must add\/change the Rule #1 name.<\/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"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","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"}]},{"discussionID":108759,"type":"question","name":"Help with formula","excerpt":"I am wanting to count the number of drop down items selected in a cell, but I only want it to count in the cell for a specific row. This is the sheet that i want to count the number of dropdown items from. This is the sheet that i want the formula written in to return an output. Currently I have =IF(HAS({centers},…","snippet":"I am wanting to count the number of drop down items selected in a cell, but I only want it to count in the cell for a specific row. This is the sheet that i want to count the…","categoryID":322,"dateInserted":"2023-08-09T20:13:45+00:00","dateUpdated":null,"dateLastComment":"2023-08-10T17:17:45+00:00","insertUserID":161673,"insertUser":{"userID":161673,"name":"Lauren Hughes","url":"https:\/\/community.smartsheet.com\/profile\/Lauren%20Hughes","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!WSHqXkuSMHM!nddov_PnFbs!Fw3jLOwOmG0","dateLastActive":"2023-08-10T17:37:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"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":"2023-08-10T17:25:14+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":28,"score":null,"hot":3383301690,"url":"https:\/\/community.smartsheet.com\/discussion\/108759\/help-with-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108759\/help-with-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108759,"commentID":390080,"name":"Re: Help with formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390080#Comment_390080","dateInserted":"2023-08-10T17:17:45+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":"2023-08-10T17:25:14+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":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/CM7LO30OUR2Q\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-10T17:16:38+00:00","dateAnswered":"2023-08-09T21:23:23+00:00","acceptedAnswers":[{"commentID":389908,"body":"

Try this:<\/p>

=COUNTM(COLLECT({July}, {Centers}, HAS(@cell, Center@row)))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","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":254,"urlcode":"Formulas","name":"Formulas"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">

Trending in Formulas and Functions