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!

Best Answer

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

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • 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?

  • 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:

    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! 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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • SKP
    SKP ✭✭✭

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

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

    @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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!