Automation Function based on athe result of a formula field


I saw a conversation on this topic a few months ago. and was wondering if anything has changed since than?

I want to create an email reminder based on a specific date that is calculated using a formula. IS that possible yet?

Thanks for your help / advice.

This is not possible. I will leave to answer why, but in essence the reason is that formula values are only calculated on the viewer’s computer. They can even be specific to the viewer’s account, containing localised time data etc. There is no cloud-based value for the result of a formula column that could be used as a trigger for an automation.

It’s a little frustrating as a user, but understandable from a technical perspective.

If you explain what the formula is and how you find that date, perhaps I can help you to reach it with an automation, in which case you could populate a real date column and use that as the trigger for a reminder notification.

Hi Francis,

I was trying to do something similar; let me explain my use case and see if you could help me with a workaround; that would be greatly appreciated:
I have a Due Date and a Baseline Date (not editable by the users); they’re able to accommodate their due dates based on their weekly workload; however, due to our internal process, a task can only be moved for 5 business days, I have a formula column that is showing the difference between these two dates, I want to trigger a notification whenever that difference is greater than 5, but I was not able to do it.

If you could share some ideas on how to accomplish this, you will help me tons!

Hi Ana. With simple automations, this is really difficult and isn’t possible without some pretty severe workarounds. For this use case, I’d typically suggest or General Caster.

Some options for avoiding using a third party tool:

If you were doing something like delaying the date each time it’s missed (which is possible) day by day, you could use a number column to keep count of the number of delays (knowing that each delay is only one day) and then use that count column hitting 5 as a trigger for some action.

But if people are just changing that column freely, there’s no way I know of in natively to detect, store, or use that value in a way that is meaningfully integrated with automations.

One other alternative is rather than trigger a notification, you could use a formula column as a status indicator.

        {Due Date},
    “Excessive Delay”,

(You might want to make this more complex to account for situations with no delay or even include the number of delay days in the display)

Once you set this up, you’ll have a really useful column indicating the delay and warning you if something has an “illegal” delay. Also combine this with conditional formatting (set the row to red if column equals “Excessive Delay”) and/or use the result to create a filtered view with only delayed items.

When that’s set up, you might find you don’t need to use notifications at all because you have a really useful monitoring view.

I think people’s first instinct is to create notifications for everything. After a while that can cause them to lose meaning or get lost in a crowd. They certainly have their place, but there are other ways of keeping track too!

Best of luck with your solution. Feel free to reach out if you have questions or need some help.

Hi Francis,

Thanks for suggestions, I will be using the conditional formatting and monitoring through “My work” section.

Appreciate the support,

1 Like