Calculate a date based on the status of another column drop down

TracyM

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!

Andrée Starå

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!

Andrée Starå

@TracyM

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!

Andrée Starå

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!

TracyM

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

Andrée Starå

@TracyM

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!

TracyM

That worked! I can not thank you enough for your help.

Andrée Starå

@TracyM

Excellent!

You're more than welcome!