Calculate a date based on the status of another column drop down
Hi,
I thought I was getting closer to figuring this one out but hit a wall! I have one column that has a dropdown menu. When one of two choices is selected, I want to calcuate a due date based upon the date a report was finalized. I'm adding a screenshot so hopefully this makes more sense.
So when Initial Evaluation Completed - DNQ or Initial Evaluation Completed - DNQ/504 Rec is chosen, I want the DNQ NOREP Due Date Column to calculate the date 30 days from the date that will be entered in the Date Finalized/Sent to LG Column.
So far I have this (I was trying to get it right with one status before adding the second status)
=IF([Initial Evaluation Status]@row, ="Initial Evaluation Completed - DNQ", [Date Finalized/ Sent to LG]@row + 30)
Right now it's calculating correctly IF the Intial Evaluation Status column is empty....when I choose "Initial Evaluation Completed - DNQ", the Due Date column changes to #INVALID DATA
Thanks for all your help! This community has helped keep me sane on my Smartsheet formula journey!
Best Answers
-
Hi @TracyM
I hope you're well and safe!
Try something like this. (you had a comma before the =)
= IF([Initial Evaluation Status]@row = "Initial Evaluation Completed - DNQ", [Date Finalized/ Sent to LG]@row + 30)
Did that work/help?
I hope that helps!
Be safe and have a fantastic week!
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, Awesome, 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.
-
Excellent!
Happy to help!
Try this. (you'd add multiple IF formulas together, and when something is true, it will stop)
= IF([Initial Evaluation Status]@row = "Initial Evaluation Completed - DNQ", [Date Finalized/ Sent to LG]@row + 30, IF([Initial Evaluation Status]@row = "Initial Evaluation Completed - DNQ/504 Rec", [Date Finalized/ Sent to LG]@row + 30))
Did that work?
✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome 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 @TracyM
I hope you're well and safe!
Try something like this. (you had a comma before the =)
= IF([Initial Evaluation Status]@row = "Initial Evaluation Completed - DNQ", [Date Finalized/ Sent to LG]@row + 30)
Did that work/help?
I hope that helps!
Be safe and have a fantastic week!
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, Awesome, 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.
-
That darn comma!! That worked.
Ok so how would I add in the second possible status selection (the Initial Evaluation Completed - DNQ/504 Rec)? I tried adding IFS to the beginning:
=IFS([Initial Evaluation Status]@row = "Initial Evaluation Completed - DNQ", ([Initial Evaluation Status]@row = "Initial Evaluation Completed - DNQ/504 Rec", [Date Finalized/ Sent to LG]@row + 30)
but that didn't work. One day these formulas will all make sense to me! lol
-
Excellent!
Happy to help!
Try this. (you'd add multiple IF formulas together, and when something is true, it will stop)
= IF([Initial Evaluation Status]@row = "Initial Evaluation Completed - DNQ", [Date Finalized/ Sent to LG]@row + 30, IF([Initial Evaluation Status]@row = "Initial Evaluation Completed - DNQ/504 Rec", [Date Finalized/ Sent to LG]@row + 30))
Did that work?
✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome 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.
-
That worked! I can not thank you enough for your help.
-
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.6K 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!