Formula & Placement for Automating Status based on End Date

Rebecca BdR

Hello Smartsheeters! I would so appreciate your help with this - I'm watching all the videos/reading the help articles, but I'm still not getting it!

Goal: Automate Status column updating

When the Status of a task is not changed to 'Complete' one day after the row's 'End/Due Date,' I want the Status to change to 'At Risk.' I believe this would be a column formula, but that option is grayed out for me. Also, where would I place the formula - in the 'Status' or 'End/Due Date' columns?

Here is the formula I have - if it's wrong, please advise!

=IF(AND([Status]@row <> "Complete", TODAY() > [Due/End Date]@row + 1), "At Risk", [Status]@row)

Thank you in advance!

ECarlson_PCG

Hi Rebecca,

If you place a column formula in your Status Column, it would mean all values would need to be derived from a formula, I'd advise against this.

I'd recommend 1/2 options below to solve for this.

1. Create a Date based automation (see images below)
   1. Go to Automation → Create from Scratch
   2. Trigger "When a Date is reached"
   3. Run Once, change dropdown next to this to "1 day after"
   4. select Date Field "End/ Due Date"
   5. Condition: Status is not one of "Complete"
   6. Action, Change Cell Value for Status to At Risk

1. Create a new column that flags a row as "At Risk" (see image below)
   1. Make a new checkbox, flag column, DO NOT restrict it so you can write a formula in the column
   2. =IF(AND(Status@row <> "Complete", [End Date]@row <= TODAY(-1)), 1, 0)
   3. Right click, scroll to the bottom and select "Convert to Column Formula"
   4. You can see row 3 as an example of the formula below:

Hope this helps!

ECarlson_PCG

Hi Rebecca,

If you place a column formula in your Status Column, it would mean all values would need to be derived from a formula, I'd advise against this.

I'd recommend 1/2 options below to solve for this.

1. Create a Date based automation (see images below)
   1. Go to Automation → Create from Scratch
   2. Trigger "When a Date is reached"
   3. Run Once, change dropdown next to this to "1 day after"
   4. select Date Field "End/ Due Date"
   5. Condition: Status is not one of "Complete"
   6. Action, Change Cell Value for Status to At Risk

1. Create a new column that flags a row as "At Risk" (see image below)
   1. Make a new checkbox, flag column, DO NOT restrict it so you can write a formula in the column
   2. =IF(AND(Status@row <> "Complete", [End Date]@row <= TODAY(-1)), 1, 0)
   3. Right click, scroll to the bottom and select "Convert to Column Formula"
   4. You can see row 3 as an example of the formula below:

Hope this helps!

Rebecca BdR

THANK YOU!! The Automation does exactly what I was looking for!! 🤩

ECarlson_PCG

Great! I'm glad it worked for you, have a nice weekend!