Formula to change task status to "At Risk" If due date is within 1 week

abbey123

Hi,

I am wondering if you can help me create a formula that will change the site status to "At Risk" if the due date is within 1 week and the status is not complete or not applicable.

Genevieve P.

So glad to hear it! 🙂

Dale Murphy

@abbey123 I would recommend using a workflow for the purpose instead of making a formula on that column. That way, any other manual changes to status can still be done.

There is a workflow that can

which is what I would recommend.

Write again if you have any trouble.

dm

abbey123

Hi Dale,

Not sure I understand what you mean by using a workflow. Can you please elaborate.

Genevieve P.

Hi @abbey123

Here's information about the Change Cell action that you can build into a workflow!

For example, you can set up an Automation to run every day, and then have the Condition Block set up to filter out rows to match your criteria, like so:

Cheers,

Genevieve

abbey123

Hi @Genevieve P.

This is so incredible helpful thank you. Which workflow template did you use?

abbey123

Hi @Genevieve P.

I actually figured out part of the workflow however I am not able to change the cell value in the status and date column that I need the status to change in. The first photo is of my sheet and the 2nd photo is of the change cell value options I have.

Genevieve P.

Hi @abbey123

I made the workflow from scratch; it looks like you were able to do this, too! 🙂

In regards to changing the cell, you won't be able to select a Date column, but a Status column should show up.

Is it a Text/Number column or a Dropdown list? You should be able to scroll down in those options to see all possible columns. (See: Change the Value of a Cell in an Automated Workflow)

Cheers,

Genevieve

abbey123

Hi @Genevieve P.

It is dropdown and none of the dropdowns popup. The only columns that pop up for me that can have the cell value change are the text/number columns.

Genevieve P.

Hi @abbey123

Can you post another screen capture after scrolling to the bottom of the list?

The Automation will first show all Text/Number columns in your sheet before moving down to the Dropdown list. Is it possible that your dropdowns are hiding at the bottom?

Can you clarify how many columns you have in your sheet?

abbey123

Hi @Genevieve P.

Gosh thank you so so much!! Everything is up and running thanks to you!

Genevieve P.

So glad to hear it! 🙂