Skip to main content
Solved

Run an automation on a variable date at a specific time

S

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

Best answer by Ben_Young1

Sharon_Visser wrote:

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.


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:


View original
    Did this topic help you find an answer to your question?

    5 replies

    Ben_Young1
    Forum|alt.badge.img+9
    • Ben_Young1
    • Inspiring
    • 520 replies
    • August 8, 2022

    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.

    S
    • Sharon_Visser
    • Author
    • Inspiring
    • 38 replies
    • August 8, 2022
    Ben_Young1 wrote:

    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.


    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
    Forum|alt.badge.img+9
    • Ben_Young1
    • Inspiring
    • 520 replies
    • Answer
    • August 8, 2022
    Sharon_Visser wrote:

    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.


    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:


    S
    1 person likes this
    • S
      Sharon_Visser
    ScottWorld
    Forum|alt.badge.img+20
    • ScottWorld
    • Brainy
    • 8718 replies
    • August 8, 2022

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

    S
    Ben_Young1
    2 people like this
    • S
      Sharon_Visser
    • Ben_Young1
      Ben_Young1
    Ben_Young1
    Forum|alt.badge.img+9
    • Ben_Young1
    • Inspiring
    • 520 replies
    • August 8, 2022
    ScottWorld wrote:

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


    oops lol

    Good catch!



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

    ScottWorld
    S
    2 people like this
    • ScottWorld
      ScottWorld
    • S
      Sharon_Visser

    Reply


    Most helpful members this week

    Terms & Conditions