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
-
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.
Answers
-
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.
-
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?
-
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.
-
I forgot to put a date in, sorry 😅 It works beautifully, thank you very much!
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!