Formula & Placement for Automating Status based on End Date

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!

Find more posts tagged with

Formulas

Automations

Workflows in Smartsheet

errors

Accepted answers

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.

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

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!

All comments

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.

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

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!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.