Skip to main content

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

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.

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.

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:



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

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

Reply


Terms & Conditions