Looking for a cell value to trigger a formula

reavesinc

I'm looking for a formula to reference the value in a column then run a formula. I am using this to check the status of a contract then run a formula to see if too much time has passed in that status.

If contract status is "In Legal" look at date sent to legal and make the value expired if seven days have past.

=IF([Contract Status]@row = "In Legal", [Sent to Legal]@row < TODAY() - 7, "Expired", "Current")

Once I get the formula the end goal is to update the status of the contract reference the date based on the status and know if the time has expired or is still current.

Example:

Contract received on DATE, we have 7 days to send to legal.

Contract sent to legal on DATE, they have 7 days to respond.

Contract received from legal on DATE, we have 7 days to return to client.

Contract executed.

Each status would have a Current/Expired status to help track time. An automation would run once a status reaches expired.

Any help in this matter is greatly appreciated. Thank you.

ker9

Personally, I try not to use TODAY() but would suggest Automation/Change Value workflow. If you have the ability to create workflows, then a daily workflow may work for you.

Trigger the workflow to run every day at a specific time (perhaps after midnight).

Condition is when "Date To Legal" field is not in the last X days (you may need additional conditions based on your ultimate goal and sheet setup)

Change Cell Value to "Expired" in the appropriate column (best if the column is a dropdown).

You may need to add an additional condition that stops this from running if something else is completed or status is different. Depends on how your sheet is setup (date received is blank or some other status field is complete).

You can add to the same workflow or create another workflow to notify someone when status is changed to expired. You could also change it to 6 days and notify someone ahead of time - this is going to expire tomorrow!

(Rough outline of the workflow option)

https://help.smartsheet.com/function/today

https://help.smartsheet.com/articles/2482767-automatically-update-today-in-formulas