Dec 31, 2020 07:40 AM
Hi Airtable Community!
I have a process that requires specific people to provide updates on the 15th of every month. I’m looking for an easy way to create a calendar that will trigger these emails automatically on the 15th of the month at 8 a.m. I’ve had some success with creating a calendar table and developing a formula that flips a field (when criteria met) to a new view that triggers email but the process is a little clunky and I haven’t been able to figure out how to send it at a certain time of day. Any advice/help is greatly appreciated!
Thank you.
Solved! Go to Solution.
Dec 31, 2020 10:34 AM
For specific time-of-day triggers, I use the HOUR()
function wrapped around NOW()
. However, keep in mind that this returns the current hour in GMT, not in your local timezone, so you’ll need to compensate in your formula calculation. For example, I have a field that triggers an automation to run every day at midnight. My timezone is GMT -8, so the formula in this field is:
HOUR(NOW()) = 8
That outputs a 1 when it’s midnight in my local time, and 0 all other hours. The automation trigger is “When record matches conditions”, with the condition being that this formula field contains a 1. It switches from 0 to 1 approximately at midnight, then resets to 0 for all other hours, so it fires once a day.
Why only approximately? This has to do with how frequently NOW()
is refreshed. There have been discussions elsewhere in the forum about this, but long story short, it varies. :slightly_smiling_face: Here’s a screenshot showing the most recent runs of this daily automation that I mentioned above (click to enlarge):
I’m fine with the fluctuation because my automation isn’t time-sensitive. Whether or not that variance will work for you is something only you can determine.
Anyway, to combine this with a specific day of the month is pretty easy. If I wanted my automation to run at 8am on the 15th, my formula (again accounting for the 8-hour time difference in my timezone) would be this:
AND(HOUR(NOW()) = 16, DAY(NOW()) = 15)
With that, you wouldn’t need a custom view, as you could also use the “When record matches conditions” trigger to see when this formula outputs a 1, which will only be at 8am on the 15th of each month.
Dec 31, 2020 10:34 AM
For specific time-of-day triggers, I use the HOUR()
function wrapped around NOW()
. However, keep in mind that this returns the current hour in GMT, not in your local timezone, so you’ll need to compensate in your formula calculation. For example, I have a field that triggers an automation to run every day at midnight. My timezone is GMT -8, so the formula in this field is:
HOUR(NOW()) = 8
That outputs a 1 when it’s midnight in my local time, and 0 all other hours. The automation trigger is “When record matches conditions”, with the condition being that this formula field contains a 1. It switches from 0 to 1 approximately at midnight, then resets to 0 for all other hours, so it fires once a day.
Why only approximately? This has to do with how frequently NOW()
is refreshed. There have been discussions elsewhere in the forum about this, but long story short, it varies. :slightly_smiling_face: Here’s a screenshot showing the most recent runs of this daily automation that I mentioned above (click to enlarge):
I’m fine with the fluctuation because my automation isn’t time-sensitive. Whether or not that variance will work for you is something only you can determine.
Anyway, to combine this with a specific day of the month is pretty easy. If I wanted my automation to run at 8am on the 15th, my formula (again accounting for the 8-hour time difference in my timezone) would be this:
AND(HOUR(NOW()) = 16, DAY(NOW()) = 15)
With that, you wouldn’t need a custom view, as you could also use the “When record matches conditions” trigger to see when this formula outputs a 1, which will only be at 8am on the 15th of each month.
Jan 04, 2021 11:57 AM
Thanks Justin! This is great, way better than what I was trying to design and exactly what I was looking for. Thanks again. Travis
May 15, 2023 01:59 PM
Hi Travis, Can you explain how you made this? I'm trying to send an email every 3 months after a record's status changes, do you have any idea on how I could automate this?