Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Struggling with a date / time calculation...

Topic Labels: Formulas
147 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Nadege_BOINNARD
6 - Interface Innovator
6 - Interface Innovator

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
0 Replies 0