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:
- 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.
- 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 monday.com or your IT strategy generally.