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.
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.