Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Apr 07, 2017 02:21 AM
I have a date field with an inlcuded time field. The box “Use the same time zone (GMT) for all collaboraters” is checked.
I have put in a date and a time (9:00).
I have made a calendar view and clicked on “share” to get my iCal subscription link. It looks like this: https://airtable.com/***/iCal?timeZone=Europe%2FBerlin&userLocale=de
I imported this feed in Apple Calendar and Google Calendar. In both the starting time is two hours off (11:00).
I have tried to replace “Europe%2FBerlin” with “GMT”, “UTC” or “Europe%2FHelsinki” but it always starts at 11:00.
What am I doing wrong?
Apr 26, 2017 12:48 AM
Does anybody have any idea how to fix this?
May 02, 2017 02:30 AM
I’m running into the same problem! My team and I are six times zones apart. Times appear accurate to both of us in Airtable, but we need the times to be locally correct after syncing with iCal. How to fix??
Jun 28, 2018 08:11 PM
Add me to the list, I’m 4 hours off. What gives.
Jun 28, 2018 10:44 PM
This is purely a guess, as I’ve not tried to interface Airtable and an external calendar, but you might want to try something like this:
DATETIME_PARSE(
DATETIME_FORMAT(
SET_TIMEZONE(
{Date},
[Your TimeZone Specifier]
),
'LLL'
),
'LLL'
)
IIRC, I’ve used that construction to beat an otherwise recalcitrant date field into shape when I couldn’t seem to get it into the correct longitude to save me…
Mar 22, 2019 02:42 PM
I’m having this issue and don’t really understand how to use this formula… Help! I really need this functionality fixed or Airtable is pretty much useless to me.
Mar 23, 2019 12:21 AM
@Susan_Lanier
What timezone do you want your dates to appear in your iCal subscription feed? In the example below, I’m converting GMT to PST (+7 hours).
I have one date field which reports dates and times in GMT because the “Use the same time zone (GMT) for all collaborators” option is on; that field is called {Start_GMT}
. This causes iCal subscriptions to be off, in my case by 7 hours because I’m in Los Angeles. To fix it, I have a formula field called {Start_PST}
that manually converts times to PST:
IF(DATETIME_FORMAT(DATEADD(SET_TIMEZONE({Start_GMT},'America/Los_Angeles'),7,'hours'),'h')=DATETIME_FORMAT(Start_GMT,'h'),DATEADD({Start_GMT},7,'hours'),DATEADD({Start_GMT},8,'hours'))
^ The formula checks if adding 7 hours to the “real” date-time of {Start_GMT}
matches the time actually displayed in the {Start_GMT}
. My timezone adopts Daylight Savings Time at a different time in the year than when the countries using GMT do, which is why the formula adds an extra 8th hour if the times don’t match after adding 7. You may or may not have to worry about daylight savings time, adjust the formula to remove the outer IF()
statement if necessary.
Create a new Calendar view using {Start_PST}
and use this calendar view to get an iCal subscription link. Note: it took several hours for my subscription in Google Calendar to update events to the correct times so be patient to see if this works for you.
Mar 27, 2019 11:39 AM
Thanks! Got it all sorted. Appreciate the help
Mar 30, 2019 03:13 PM
FYI, to get around having to hardcode time offset from GMT or to track DST variations, you can use the 'ZZ'
specifier with DATETIME_FORMAT()
and DATETIME_PARSE()
to shift hours (and, if applicable, minutes) automatically. For details, see Item 2 in this reply.
Sep 07, 2019 08:28 AM
Sept of 2019 and this problem is still around.
ical subscription link seems to have the timezone data in the URL, but both google and microsoft default to UTC or GMT or whatever.
The parsing formula suggested by others is insufficient because although it will make the time display correctly on the receiving calendar, when someone clicks an event link, and arrives at the airtable calendar, they will see the wrong (adjusted) times on all the events.