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

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

    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.

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

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

    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

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

    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.

  • TracyM
    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å
    Andrée Starå ✭✭✭✭✭✭
    Answer ✓

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

    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.

  • TracyM
    TracyM ✭✭✭

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

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @TracyM

    Excellent!

    You're more than welcome!

    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!