Update a Status Formula

Options
RSanders
RSanders ✭✭✭
edited 12/09/19 in Formulas and Functions

Hi, 

I currently have the following formula for a status column:

=IF([Expiry Date]78 < TODAY(), "Expired", IF([Expiry Date]78 < TODAY() + 60, "Expiring Soon", "Current"))

However, I have now added a column that is a checkbox titled "Re-Training Not Required" and another checkbox column titled "Inactive" (If the checkbox is checked it will do the formula as above)

Is there anyway to add to the formula that if the 're-training not required' is checked it will mark status column as 'current' and if 'inactive' is checked it marks the status as 'inactive'?  (If the checkbox is checked it will do the formula as above)

Thanks for your help. 

 

Tags:

Comments

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

    Hi,

    Not sure about the order you want it, and also I'm not sure about the below. It can't do both so I interpreted it as if the checkbox isn't checked it should continue with the original formula.

    I also updated the row reference to @row, so you don't have to think about the row numbers.

    Is there anyway to add to the formula that if the 're-training not required' is checked it will mark status column as 'current' and if 'inactive' is checked it marks the status as 'inactive'?  (If the checkbox is checked it will do the formula as above)

    Try something like this.

    =IF([Re-Training]@row = 1; "Current"; IF(Inactive@row = 1; "Inactive"; IF([Expiry Date]@row < TODAY(); "Expired"; IF([Expiry Date]@row < TODAY(60); "Expiring Soon"; "Current"))))

    The same version but with the below changes for your and others convenience.

    =IF([Re-Training]@row = 1, "Current", IF(Inactive@row = 1, "Inactive", IF([Expiry Date]@row < TODAY(), "Expired", IF([Expiry Date]@row < TODAY(60), "Expiring Soon", "Current"))))

    Depending on your country you’ll need to exchange the comma to a period and the semi-colon to a comma.

    Did it work?

    Have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant @ Get Done Consulting

    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.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Options

    =IF([Re-Training Not Required]@row = 1, "Current", IF(Inactive@row = 1, "Inactive", IF([Expiry Date]@row < TODAY(), "Expired", IF([Expiry Date]@row < TODAY(60), "Expiring Soon", "Current"))))

    I made a slight modification to account for the Re-Training column name you said you had added in your original post. . 

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

    Good catch Mike!

    I missed the Not Required part of the name obviously.

     

    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å ✭✭✭✭✭✭
    Options

    EDIT: Updated the formula

    =IF([Re-Training Not Required]@row = 1; "Current"; IF(Inactive@row = 1; "Inactive"; IF([Expiry Date]@row < TODAY(); "Expired"; IF([Expiry Date]@row < TODAY(60); "Expiring Soon"; "Current"))))

    The same version but with the below changes for your and others convenience.

    =IF([Re-Training Not Required]@row = 1, "Current", IF(Inactive@row = 1, "Inactive", IF([Expiry Date]@row < TODAY(), "Expired", IF([Expiry Date]@row < TODAY(60), "Expiring Soon", "Current"))))

    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!