Does anybody have any idea how to fix this?
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??
Add me to the list, I’m 4 hours off. What gives.
Add me to the list, I’m 4 hours off. What gives.
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…
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…
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.
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.
@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.
@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.
Thanks! Got it all sorted. Appreciate the help
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.
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.
Hi all, I’m confused because I’m only using GMT, but when I subscribe to the calendar through the ical link in google calendar, the whole calendar is 4 hours off. Not sure what I’m doing wrong.