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.

Find more posts tagged with

Formulas

Comments

Andrée Starå

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

Mike Wilday

=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å

Good catch Mike!

I missed the Not Required part of the name obviously.

Andrée Starå

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"))))

Sign in to join the conversation!

Quick Links

Trending discussions

Netdays Formula
I am wanting to calculate Netdays but only if there is data in 'Corrective Action 1' I am currently using this formula: =IF(ISBLANK([Date Action 1 Complete]@row ), NETDAYS([Date of Event]@row , TODAY()), NETDAYS([Date of Event]@row , [Date Action 1 Complete]@row )) How do I adjust it to only pull a date when Corrective…
Re-write a formula to make it Column Formula compatible
I have Concept phase projects that can result in multiple Phase 1 projects being generated from one Concept Review. I want to calculate the time in the Concept Phase for the first project from that Concept (the Approved Gate 0 UID), but mark it N/A for any subsequent references to that UID. I figured out the formula below…
How to split contents from a cell from a smartsheet into another smartsheet
I need to extract and split the contents of a cell in a Smartsheet into three separate columns to a new smartsheet. The cell entries are originally submitted through a form.