Skip to main content

I have been trying to create an automation with a Google Calendar trigger where, upon the creation of a new calendar event, a record is created in my Airtable base. I can look at the appointment in my Google Calendar (9/3/22 from noon to 1pm). When I look in my base, the start and stop time values are displayed as 5:00pm and 6:00pm.


The Title, Date, Start/Stop Times of the Calendar Event read as follows (in Google Calendar):


“Test Event 3

Sep 3, 2022 12:00pm to 1:00pm Sep 3, 2022 (GMT-5:00) Central Time - Chicago time zone”


I went back to check my automation routine in Airtable and, upon generating a preview, it shows start and stop times of 5:00pm and 6:00pm. The formula I used to translate the GMT (Start and End) fields into the correct time format is below. The format is fine. The values are wrong by 5 hours.


DATETIME_FORMAT(SET_TIMEZONE({Begin GMT},‘Chicago/Ilinois’), ‘hh:mm a’)


Any suggestions? Thanks in advance.

Hey @Bobby_Gay!


All the pieces are in the correct place. However, the time zone specifier parameter in the SET_TIMEZONE function isn’t valid.


Give this formula a shot:


DATETIME_FORMAT(

    SET_TIMEZONE(

        {Begin GMT},

        'America/Chicago'

    ),

    'hh:mm a'

)

Here’s a link to the supported parameters for the formula.


While the supported specifiers aren’t comprehensive, they do provide full coverage.


It might’ve been confusing since the editor doesn’t throw you a syntax error upon saving with the Chicago/Illinois parameter, but I’m pretty sure that’s your culprit.

Hey @Bobby_Gay!


All the pieces are in the correct place. However, the time zone specifier parameter in the SET_TIMEZONE function isn’t valid.


Give this formula a shot:


DATETIME_FORMAT(

    SET_TIMEZONE(

        {Begin GMT},

        'America/Chicago'

    ),

    'hh:mm a'

)

Here’s a link to the supported parameters for the formula.


While the supported specifiers aren’t comprehensive, they do provide full coverage.


It might’ve been confusing since the editor doesn’t throw you a syntax error upon saving with the Chicago/Illinois parameter, but I’m pretty sure that’s your culprit.


Bullseye! Worked like a charm. Thanks!

Hey @Bobby_Gay!


All the pieces are in the correct place. However, the time zone specifier parameter in the SET_TIMEZONE function isn’t valid.


Give this formula a shot:


DATETIME_FORMAT(

    SET_TIMEZONE(

        {Begin GMT},

        'America/Chicago'

    ),

    'hh:mm a'

)

Here’s a link to the supported parameters for the formula.


While the supported specifiers aren’t comprehensive, they do provide full coverage.


It might’ve been confusing since the editor doesn’t throw you a syntax error upon saving with the Chicago/Illinois parameter, but I’m pretty sure that’s your culprit.


Thanks Ben. It works now!

Reply


Terms & Conditions