Formula Issue

Options

I am trying to create a formula in Smartsheet, which I am able to make work in Excel. However, the formula isn't working in Smartsheet. Does anyone have a solution?

What I want to do is calculate the next due date based on the current status and the date the status was updated.

=IF([Program Status]@row = “Apprenticeship Program - Phase 1”, SUM([Last Status Change]@row+120),0)

Dr. Scott Tierno, D.A., PMP

Director of the Academic Leadership Academy

Unitek Learning

Direct: 949-516-9215

Best Answer

Answers

  • Christina09
    Christina09 ✭✭✭✭✭✭
    Options

    Hi @tiernosc

    You're very close, but you don't need the SUM formula

    =IF([Program Status]@row = “Apprenticeship Program - Phase 1”, [Last Status Change]@row + 120)

    Make sure the column Anticipated Date Until Next Phase is a Date column

  • tiernosc
    tiernosc ✭✭✭
    Options

    I'm still getting "Unparseable" as a response...

    Dr. Scott Tierno, D.A., PMP

    Director of the Academic Leadership Academy

    Unitek Learning

    Direct: 949-516-9215

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

    Hi @tiernosc

    I hope you're well and safe!

    Try something like this.

    =
    IF([Program Status]@row = "Apprenticeship Program - Phase 1", 
    [Last Status Change]@row + 120)
    

    Did that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    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.

  • tiernosc
    tiernosc ✭✭✭
    Options

    I also have 3 more status changes that I want to add to the string. Here is what I tried to do, but received an Unparseable message:

    =IF([Program Status]@row = "Apprenticeship Program - Phase 1", [Last Status Change]@row + 31), IF([Program Status]@row = "Submit Preliminary Credential Application - Phase 2", [Last Status Change]@row + 31), IF([Program Status]@row = "Preliminary Credential - Phase 3", [Last Status Change]@row + 190)

    So, what I want to do is when the status is manually changed to either Phase 2 or 3 I want to add to the date in the Last Status Change column and display it under the Anticipated Date Until Next Phase column.

    Dr. Scott Tierno, D.A., PMP

    Director of the Academic Leadership Academy

    Unitek Learning

    Direct: 949-516-9215

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!