Automating Change of Status if not completed with 1 week to go

Hi Guys.
I’m trying to create an automation that changes the status to: “In Last Week”, if the date is within 7 days of the “QBR Date”, but it doesn’t seem to be working,

What am doing wrong here?

Just to add, I have an integration using “Start + End = Timeline” that generates the timeline column using a “Start Date” column and “QBR Date” column - hoping this isn’t impacting anything - not sure why it would.

Have uploaded some images:

  • One of the automation
  • One of the board - showing the status not changing even though its in the date range.

Please help!

Hi THere!

Your integration you have with this date column is impacting your dates and automations. I would go the route instead of subitems that outline tasks and milestone meeting for each event so you can still have individual dates and summarize the date columns to a timeline in the parent item.

This will work then with your automations you need as well.

Hi @T1mb0,

The issue here is that you’re expecting the 1 week before to act as a condition. Really it is acting as a trigger.

So let’s say it is 8 days before the QBR date arrives. The following day, this will trigger and your status will change.

However, if the QBR Date is set to a date that is already <1 week away from the current date, it will never cross this threshold and the automation you showed won’t trigger.

What’s the solution?

I solved a similar problem here: Change status if date column is within xx months - #2 by FrancisElliott

Essentially, you have two options, of which you can use either or combine them:

  1. Duplicate this formula 7 times and use different triggers (i.e. 6 days before, 5 days before … 1 day before etc.). This seems like it would really help, as you want to make sure the notification is sent. Consider adding a status column “Notification Sent?” to use as a filter. “And only if Notification Sent? is No / “And set Notification Sent? to Yes would prevent a notification sending every day.
  2. Use the formula column to indicate this. The formula column won’t act as a trigger, so it won’t send a notification, however it will allow you to display the information. As best practice, I would definitely recommend using a different column to indicate <1 week away anyway. It’s not technically a Status, so it muddies the waters of your model to represent that information in the same column. I don’t know what other statuses you keep in there, but it might be helpful to see <1 week away and Stuck or <1 week away and Not Started or <1 week away and In Progress. If you just change that column to <1 week away, you might be masking useful data.

Please feel free to reach out to me directly for help with or your IT strategy generally.