Generating dates based on dropdown choice in another cell

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!

Find more posts tagged with

Formulas

Automations

Accepted answers

Andrée Starå

@SKP

Haha! Easy to miss!

Excellent!

You're more than welcome!

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking 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!

All comments

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

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking 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!

SKP

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?

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.

SKP

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

Andrée Starå

@SKP

Haha! Easy to miss!

Excellent!

You're more than welcome!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.