Trigger based on date

[Evan_Rothrock]

Hello, I have an automation that uses a date field as the trigger. When I test it, it runs successfully, but the record it finds is incorrect.

Today is 10/7. The Trigger is set to “When [Date Field] [is] [yesterday]”, the test runs successfully, but the record it find has a date field dispositioned as 10/5, not 10/6.

Is this because of the time of day it is currently for me? Is there a server time that triggers are based on? Can I change my timezone somewhere so this is resolved?

Thanks!

[JonathanB]

When you run a “test” in automations, it picks a record that matches the criteria at that time. If you continue running tests it will still use the same record, even if it no longer matches (strange I know, but it’s something I’ve noticed) unless you remove that record or run the test from scratch. If you turn the automation on and make a new dummy record that meets the criteria today, the automation should run on that.

Another thing you can do is use the NOW() function to make a “time since record created/modified” field using DATETIME_DIFF and set your automation to run when the time since the record was made/modified is less than 10 minutes ago, for example.

[Justin_Barrett]

Evan_Rothrock:

Is this because of the time of day it is currently for me? Is there a server time that triggers are based on?

Many time-based calculations in Airtable are made using GMT. However, after running several tests, it appears that the “yesterday” option works as expected when a date field is set to local time. So even if the GMT version of the time isn’t yesterday, it will be seen as yesterday as long as the date field in question is not set to display the time as GMT.

However, these initial tests were run with an actual date field. If the date field being checked by your automation is a formula field that outputs a date—e.g. you’re using DATEADD() to calculate a new date based on an actual date field—it will behave differently. As an extension of my test, I made a formula field that did a straight copy of the date from a date field. Both fields were set to not use GMT to display the results, and yet the formula field showed a date that was one day earlier than the actual date field. However, the record with a matching “yesterday” date in the date field still triggered the automation using the formula field even though the displayed date from the formula field was two days prior, not just one.

The fix to get this formula field to display the correct date is to turn on GMT option for the field. It may seem counterintuitive, but it works.