Update a Status Formula

RSanders

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. 

 

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