Generating dates based on dropdown choice in another cell

SKP
SKP ✭✭✭
inFormulas and Functions

Hi everyone,

I'm very new to this and trying to generate a formula that will calculate deadline dates based on dropdown choices in another cell. If we get customer queries by phone, we have a deadline of 2 days' resolution; if we get them by email, it's 10 days. I would like to be able to auto-generate deadline dates for colleagues' reference.

Internal/External is the dropdown column that the colleague will choose to indicate how the query came up

Date Raised is the date column where they state when they received it.

I've gotten this far:

=IF([Internal/External]="Phone", "[Date Raised]+2"), IF([Internal/External]="Email", "[Date Raised]+10"), IF([Internal/External]="Internal", "The responsible colleague should set a reasonable resolution deadline - please enter now"

Also, aside from that, the rows are entered via form. Is there a way for new rows to automatically have this formula in so that again, it can be as automatic as possible?

Thanks everyone!

Tags:
· Share on Facebook Share on Twitter

Best Answer

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    @SKP

    Haha! Easy to miss!

    Excellent!

    You're more than welcome!

    ✅Remember!我的帖子(s) help or answer your question or solve your problem? Please support the Community bymarking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    https://workbold.com/

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

    · Share on Facebook Share on Twitter

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi@SKP

    I hope you're well and safe!

    Try something like this.

    = IF([Internal/External]@row = "Phone", [Date Raised]@row + 2, IF([Internal/External]@row = "Email", [Date Raised]@row + 10, IF([Internal/External]@row = "Internal", "The responsible colleague should set a reasonable resolution deadline - please enter now")))

    Did that work/help?

    I hope that helps!

    Have a fantastic weeend & Happy New Year!

    Best,

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    ✅我的帖子(s) help or answer your question or solve your problem? Please support the Community bymarking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    https://workbold.com/

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

    · Share on Facebook Share on Twitter
  • SKP
    SKP ✭✭✭
    edited 01/07/22

    Hi Andrée,

    So, the internal query one did work, thank you!

    Phone and email aren't generating dates though, sadly. I had a look at the Date Raised column and it's definitely a 'date' column...not sure what else to check though.

    Also, for when this data is entered by form, how can I make sure the formula is in the new rows, please?

    Thank you very much!

    Sarah


    Edit: Also I have a similar query about returning a colleague name (but the imported email contact type of name, just not the name) in a column based on a drop-down menu in another column (with the goal of a workflow then emailing them to tell them they need to visit the sheet and action it) - can I ask here or would a new, separate question be more appropriate?

    · Share on Facebook Share on Twitter
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @SKP

    Happy to help!

    Strange! It should work! I tested it, and it works for me for all options.

    What happens?

    To have it automatically added, you'd convert the formula to a so-called Column Formula.

    More info:

    https://help.smartsheet.com/articles/2481944-set-formulas-for-all-rows-with-column-formulas

    Regarding the similar query. It's best to submit it as a new post. Feel free to @ mention me, and I'll take a look.

    ✅Remember!我的帖子(s) help or answer your question or solve your problem? Please support the Community bymarking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    https://workbold.com/

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

    · Share on Facebook Share on Twitter
  • SKP
    SKP ✭✭✭

    I forgot to put a date in, sorry It works beautifully, thank you very much!

    · Share on Facebook Share on Twitter
  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

    @SKP

    Haha! Easy to miss!

    Excellent!

    You're more than welcome!

    ✅Remember!我的帖子(s) help or answer your question or solve your problem? Please support the Community bymarking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    https://workbold.com/

    Andrée Starå| Workflow Consultant / CEO @WORK BOLD

    W:www.workbold.com| E:[email protected]| P: +46 (0) - 72 - 510 99 35

    Feel free to contact me about help with Smartsheet, integrations, general workflow advice, or something else entirely.

    · Share on Facebook Share on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

想实践工作直接与公式Smartsheet?

Check out the公式手册模板!

Help shape the future of Smartsheet.
Share your ideas and feature requests.

 Watch video
Try removing one of the closing parenthesis after the DATE function.<\/p>"},{"commentID":388644,"body":"

Thanks! That sorted it!😀<\/span><\/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":108399,"type":"question","name":"Smartsheet Behaviours","excerpt":"I am trying to create a form that throws back a response when you tick a box like the example below. I have been told to use the logic but i cant seem to find a logic that will work, any suggestions? Additionally, if they tick an answer and it is incorrect, I want to show the text in red and if correct the text is in…","snippet":"I am trying to create a form that throws back a response when you tick a box like the example below. I have been told to use the logic but i cant seem to find a logic that will…","categoryID":322,"dateInserted":"2023-08-02T07:07:49+00:00","dateUpdated":"2023-08-02T11:36:01+00:00","dateLastComment":"2023-08-03T13:01:11+00:00","insertUserID":161048,"insertUser":{"userID":161048,"name":"Julieh","url":"https:\/\/community.smartsheet.com\/profile\/Julieh","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-02T23:38:32+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":"2023-08-03T13:51:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":29,"score":null,"hot":3382029540,"url":"https:\/\/community.smartsheet.com\/discussion\/108399\/smartsheet-behaviours","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108399\/smartsheet-behaviours","format":"Rich","lastPost":{"discussionID":108399,"commentID":388680,"name":"Re: Smartsheet Behaviours","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388680#Comment_388680","dateInserted":"2023-08-03T13:01:11+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-03T13:51:06+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\/TZJ2NJ1GP5G8\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-03T09:08:52+00:00","dateAnswered":"2023-08-02T12:54:16+00:00","acceptedAnswers":[{"commentID":388451,"body":"

You would have to insert a new header field into the form that has that particular verbiage formatted the way you want then use the form field logic to display this header row if one of the incorrect answers is selected.<\/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":108391,"type":"question","name":"Can I use groups to create series?","excerpt":"I have this report and from this I have created this Chart but I want it to drill down to the show the individual Branches on a stacked column chart like this. is this at all possible?","snippet":"I have this report and from this I have created this Chart but I want it to drill down to the show the individual Branches on a stacked column chart like this. is this at all…","categoryID":322,"dateInserted":"2023-08-02T02:31:48+00:00","dateUpdated":"2023-08-02T11:36:34+00:00","dateLastComment":"2023-08-03T13:06:28+00:00","insertUserID":163489,"insertUser":{"userID":163489,"name":"Benny C","url":"https:\/\/community.smartsheet.com\/profile\/Benny%20C","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!xq_RMfil_3w!!B9mU9H6NId1","dateLastActive":"2023-08-03T00:58:31+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":"2023-08-03T13:51:06+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3382013296,"url":"https:\/\/community.smartsheet.com\/discussion\/108391\/can-i-use-groups-to-create-series","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108391\/can-i-use-groups-to-create-series","format":"Rich","lastPost":{"discussionID":108391,"commentID":388682,"name":"Re: Can I use groups to create series?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/388682#Comment_388682","dateInserted":"2023-08-03T13:06:28+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-03T13:51:06+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\/UYHRDRG9I6TS\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-03T00:59:37+00:00","dateAnswered":"2023-08-02T12:47:07+00:00","acceptedAnswers":[{"commentID":388443,"body":"

This isn't possible in a report. You would have to use a specifically structured metrics sheet with cross sheet references pulling in your totals.<\/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":[]}],"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

  • Date formula to set cut off period

    I am trying to set a date formula to for the following statement; if the [Next Due Date] is <= 27/07/2023 return "Performed" Example: =IF(ISBLANK([Last inspection]@row), "", IF(DATE(2023,7,27)) < [Next Due Date]@row, "Overdue", "Performed"))

  • Smartsheet Behaviours

    I am trying to create a form that throws back a response when you tick a box like the example below. I have been told to use the logic but i cant seem to find a logic that will work, any suggestions? Additionally, if they tick an answer and it is incorrect, I want to show the text in red and if correct the text is in…

  • Can I use groups to create series?

    I have this report and from this I have created this Chart but I want it to drill down to the show the individual Branches on a stacked column chart like this. is this at all possible?