How do I automate At Risk flag to change to red 72 hours prior to deadline/task end date?

April Tucker

Hi - I'm trying to automate the At Risk flag to change to RED 72 hours prior to the task deadline so it can trigger an email to the person assigned to the task. I have created the workflow/alert email upon red flag trigger, so that part is done, but I have not found the right formula to change the At Risk flag automatically vs. manually. I have tried a few different returns, but the only one that works so far is 1, 0, whereby 1 means the task is within the 72 hour trigger window and not marked as Complete (status) and 100% Complete (% Complete). Here's what I have - seeking a solution. Much appreciated!!

Paul Newcome

Your formula in your above screenshot is pointing at the START DATE and not the End Date.

April Tucker

Paul Newcome

To flag the [At Risk] column, you need your output to be 1 and the formula needs to be in the [At Risk] column.

I am not sure exactly what you are trying to accomplish with the portion of your formula that you have underlined and the location itself though.

April Tucker

Hi Paul - thank you for replying! I'm VERY new to Smartsheet formulas and open to any suggestions on how to get the At Risk flag to automatically turn to red when the deadline is within 72 hours. Can you help? Here's my adjusted formula inserted into the At Risk column for one of my task lines, but it doesn't work (no surprise, I'm sure). Thank you!

Paul Newcome

Remove this part

[AtRisk]@row,

including the comma and space before the 1.

April Tucker

Hi Paul,

Actually, that's the formula I started with, but as shown below, it is not changing the flag to red. I have placed the formula in both the At Risk and the Health columns to show you that the results are coming back as a 1 or a 0. If I remove the 0, it populates and error. Thoughts?

Paul Newcome

Your formula in your above screenshot is pointing at the START DATE and not the End Date.

April Tucker

So wierd - I don't even recall putting that in! Maybe I accidentally grabbed it in my many formula attempts, BUT that solved it - I'm good to go! Thank you sooooo much :)

April Tucker

Paul Newcome

Haha. I do that too sometimes. Glad we were able to get you up and running. 👍️

April Tucker

Hi Paul,

I realized reviewing the project plan today that my formula doesn't allow for including overdue dates in the flags, so the flags that were there on Friday are no longer marked At Risk. Do you know how I can resolve this, or should I create a new question in the community group? Please let me know either way.

Thank you!!

Paul Newcome

Try removing the [End Date] > TODAY(), section.

April Tucker

Works perfectly - thank you again so much!!

Paul Newcome

Excellent! Happy to help! 👍️