Update a Status Formula
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.
Comments
-
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.
-
=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. .
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!