Hello, I am trying to get this to work but all I get so far is a massive headache haha 🙂
Context : organizing automatic reminders to clients we're meeting at a trade show. On the day of the meeting, I want to send a reminder to the invitee at 8:00 local time (and of course this local time changes depending on where the trade show is) to tell them they have a meeting with us that day.
I have two tables.
> One for trade shows with Trade Show Name (linked to the other table) and various fields, one being a number field providing the hours difference as well as a time zone field 'Europe/Paris' for instance, this being the time zone where the trade show takes place.
> One for meetings with plenty of fields, including one that comes from our booking tool (Calendly) and where I've set up a webhook and the meeting time always comes as UTC. I have there also a lookup field repatriating the hours difference as well as the timezone field.
How do I create a field that will set the time as 8:00 local time, with the day of the meeting so I can use this field to trigger the automation ("when record matches condition > that field = NOW()") or something similar ?
I'm struggling SO MUCH with those date / time fields when it comes to time zones...
My local time meeting field is not considered as a date / time by Airtable for some reason so I can't use it to make other calculations (I get NaN results or ERROR).
My calculation for Meeting Slot Local Time is :
DATETIME_FORMAT(SET_TIMEZONE({Meeting Slot UTC},{Time Zone}),'DD/MM/YY - HH:mm')
My settings for the Meeting Slot UTC is ISO as Date Format, Include Time and Display Time Zone.
Thank you all in advance for your help !
Nadège
