Run an automation on a variable date at a specific time

[Sharon_Visser]

Hi Airtable Community,

I have created new fields and an automation to allow me to automatically send out an email and Teams message 2 days prior to an observed holiday in our Company Calendar table. I have successfully figured out the date when the automation should fire, but now I’d like to take it a step further and ensure that it goes out at 9:00AM EST on the reminder date. Here are the relevant details:

1. I have a formula field called “Remind Today?” that will either be set to “Yes” or “No”.
   Formula: IF(Category=“Observed Holiday”,IF({Reminder Date}=TODAY(),“Yes”,“No”),“”)
2. ‘Reminder Date’ is a formula field. In the Formatting tab, I’ve selected “Date Format” = Local, Include Time, 12 Hr format, and use the same time zone (GMT) for all users.
3. Automation triggers on conditions: Category = “Observed Holiday” and “Remind Today?” = “Yes”.

Since the ‘Remind Date’ field shows a date with time = “12:00am”, I thought the automation would fire at midnight, but it actually fired at 8:52pm the night before. I assume it went out at the GMT time (?).

What can I do to make the automation fire at 9:00am EST on the Reminder Date?

Thanks in advance!!

[Ben_Young1]

Hey @Sharon_Visser!

For this, I recommend leveraging the SET_LOCALE function.
This will allow you to disregard the GMT settings/formatting and hard-code the timezone to fit your needs.

Here’s how I did it:

IF(
    {Category} = "Observed Holiday",
    IF(
        {Reminder Date} = TODAY(),
        DATETIME_FORMAT(
            SET_LOCALE(
                {Reminder Date},
                'America/New_York'
            ),
            'LLL'
        )
    )
)

Here’s what that formula outputs.
I formatted it with the LLL parameter for the sake of showing that the time value is 12:00 AM EST.

As a side note, not sure if it’s a hard requirement, but I think you can consolidate your two formula fields into one by nesting them.

It’s actually how I did the formula above.

You can then set up your automation to fire whenever the formula field is not empty.
It’ll save you a field and keep things tidy, but it’s a stylistic thing.

[Sharon_Visser]

Thanks for responding so quickly! If I understand what you are saying, using SET_LOCALE with ‘America/New_York’ will set it to EST at midnight. But, let’s say I wanted the automation to go at a specific time in EST, not midnight but 9:00 AM. Would the ‘LLL’ format help me do that? If I can’t get the specific time I want, then midnight will definitely work.

[Ben_Young1 · Accepted solution]

Ooooo!

So, if you want to have it go for 9:00 AM EST, then you’ll want to nest a DATEADD function inside of the formula like so:

IF(
    {Category} = "Observed Holiday",
    IF(
        {Reminder Date} = TODAY(),
        DATETIME_FORMAT(
            SET_LOCALE(
                DATEADD(
                    {Reminder Date},
                    9,
                    'hours'
                ),
                'America/New_York'
            ),
            'LLL'
        )
    )
)

Here’s the behavior this returns:

[ScottWorld]

@Ben.Young You meant to say SET_TIMEZONE, not SET_LOCALE.

[Ben_Young1]

oops lol
Good catch!

---

@Sharon_Visser, just a heads up while I edit things.