The problem is that Airtable doesn’t trigger automations properly. It doesn’t wait until you have finished typing in the field to trigger the automation… it triggers as soon as you BEGIN typing in the field. Therefore, as soon as you type even one single number into the date field, Airtable sees that as a date that is earlier than today. (Alternatively, when you choose a date from the pop-up calendar, the entire date gets pasted in at once, which is why doing that avoids this problem.)
You can see that this erratic behavior makes very little sense, and you can see why this frustrates & confuses almost everybody who tries to use automations. But the more you work with Airtable, the more you’ll realize that almost nothing in Airtable has been fully thought-out properly, so using Airtable simply becomes a matter of working around all the “gotchas” like this on a regular basis.
Typically, the simplest way to workaround this landmine in any of your automations is to simply use a checkbox field to trigger automations. In your situation, you could make your trigger based on TWO conditions: (1) the date is earlier than today, AND (2) the checkbox field is checked. And then, don’t check the checkbox until you’ve finished typing in the date.
Another way to workaround this Airtable flaw is to delay your automation so that it doesn’t happen immediately. You can create a formula field that keeps track of the number of minutes that have passed since you last edited the date field (keeping in mind that even typing in one character into the date field counts as the last time you edited the field), and then have the automation trigger when this formula field is greater than the number 1 or 2. The problem with this is yet another Airtable flaw: Airtable only updates its internal clock every 10 minutes or so while the base is open, so this can take up to 10 minutes to trigger if your base is open. If you close your base, it can take an hour or two to trigger. (For quicker & more reliable scheduling, you can use a professional automation tool like Integromat which triggers every 1 minute or instantly, depending on how you set it up,)
I’m sure that there are many other workarounds to this Airtable problem as well. For example, you could use a button to run a script that asks users what the end date is, and then the script can paste in the entire date at once.
Others will hopefully chime in with other workarounds too! :slightly_smiling_face: As I mentioned above, I’m sure that there are many other ways to workaround this flaw, and I’d love to learn about everyone’s ideas on this.