Help w/ IF formula

jmo
jmo ✭✭✭✭✭✭
edited 09/26/22 in Formulas and Functions

Hi team – I have the following columns:

 

I want to only have the RISK AGE column show a number (count of days) if the STATUS = ACTIVE.

 If the STATUS = INACTIVE then the RISK AGE column needs to be blank.

 Column configurations:

  • STATUS – drop down menu (Active, Inactive)
  • DATE RISK IDENTITIED – date
  • DATE OF LAST RISK REVIEW – date
  • Risk Age – formula currently used: =IF(ISBLANK([Date Risk Identified*]1), "", IF([Risk Status*]1 = "closed", "", IF(ISBLANK([Date of Last Risk Review*]1), TODAY() - [Date Risk Identified*]1, TODAY() - [Date of Last Risk Review*]1)))
    • This appears to be working as needed HOWEVER, Smartsheet will not allow me to convert the column to a formula

 

Appreciate the help on this.

Best Answer

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

    Hi @jmo

    I hope you're well and safe!

    Try something like this. (you can't have specific row references in a column formula)

    =IF(ISBLANK([Date Risk Identified*]@row), "", IF([Risk Status*]@row = "closed", "", IF(ISBLANK([Date of Last Risk Review*]@row), TODAY() - [Date Risk Identified*]@row, TODAY() - [Date of Last Risk Review*]@row)))

    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 help the Community by marking it as the accepted answer/helpful. 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 @jmo

    I hope you're well and safe!

    Try something like this. (you can't have specific row references in a column formula)

    =IF(ISBLANK([Date Risk Identified*]@row), "", IF([Risk Status*]@row = "closed", "", IF(ISBLANK([Date of Last Risk Review*]@row), TODAY() - [Date Risk Identified*]@row, TODAY() - [Date of Last Risk Review*]@row)))

    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 help the Community by marking it as the accepted answer/helpful. 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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!