Help with a formula to auto change the status column.

Emily Fuller

Hello,

Hoping someone can help me with a formula, if its possible.

I'd like a formula to change the status to "Upcoming" if the date is 30 days in the future of the start date and completed date is blank, and update to "overdue" if target date is 1 day past, and completed date is blank.

Jason P

Hi Emily.

I think a workflow might better meet your needs. We use workflows along with conditional formatting to highlight rows &/or cells when projects dates or other conditions change.

In the two examples below, the first one aligns with your request while the second one alerts someone when the project is overdue and changes the status to overdue. Alerting someone will require you to establish a new [Contact List] column and populate it.

Note: Target date is in the past. Cannot set to 1 day past however as the trigger runs every morning and the condition is look at a date in column [Target Date] where Is in the Past it accomplishes the same.

Alert & change Cell.

Would either of these work for you? word of caution, with so many options workflows can be addictive.... 😉

Other resources: https://help.smartsheet.com/topics/Automated%20Workflows and. https://help.smartsheet.com/learning-track/level-1-get-started/conditional-formatting

KPH

Hi @Emily Fuller

I have made some assumptions about the brief, which I hope are correct

• "Upcoming" if the target date is 30 days in the future of the start date and completed date is blank
• "overdue" if target date is 1 day or more in the past, and completed date is blank.
• "overdue" If the target date is 30 days after the start date but also 1 or more days in the past and completed date is blank

This formula should do that for you

=IF(ISBLANK([Completed Date]@row), IF([Target Date]@row < TODAY(), "overdue", IF([Target Date]@row > [Start Date]@row + 30, "Upcoming")))

I will talk you through how I built the formula, so you can adapt it for other purposes, and troubleshoot.

Firstly we look at Completed Date (as that applies to all the logic) and create a formula to check whether this is blank or not, like this:

=IF(ISBLANK([Completed Date]@row), "completed date is blank", "has completed date")

For now, this is putting in text so we can see what is happening:

We can see that this works. As we don't want anything to appear if the completed date is populated, we can delete that last part, and just use this

=IF(ISBLANK([Completed Date]@row), "completed date is blank")

Now we need a second formula to replace the text that is currently appearing with overdue, where appropriate. That formula is

=IF([Target Date]@row < TODAY(), "overdue")

We can nest that into the first formula like this. The bold part replaces the text we had before

=IF(ISBLANK([Completed Date]@row), IF([Target Date]@row < TODAY(), "overdue"))

That gives us this:

And finally, we need a third formula for Upcoming. That formula is:

=IF([Target Date]@row > [Start Date]@row + 30, "Upcoming")

And we need to also add that into the formula we have been building. I have edited the formula above and put some text in to show where it goes.

=IF(ISBLANK([Completed Date]@row), IF([Target Date]@row < TODAY(), "overdue", "not overdue and not completed"))

Gives

We want the Upcoming formula to be evaluated if the status hasn't already been changed to overdue, so it replaces this bold part. So the complete formula is

=IF(ISBLANK([Completed Date]@row), IF([Target Date]@row < TODAY(), "overdue", IF([Target Date]@row > [Start Date]@row + 30, "Upcoming")))

I had to add an extra row to my example as I didn't have any that were true for this, but here we have it:

You can then make this a column formula.