Struggling with a date / time calculation...

[Nadege_BOINNARD]

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

[MJen]

Hi,

Working with time zones and date/time calculations is a challenge 🙂

First, make sure your {Meeting Slot UTC} field is properly formatted. It should include the time and be set to display in the same time zone (GMT). This ensures consistency when performing calculations.

Next, you’ll need to convert the meeting time to the local time of the trade show using the hours difference. You can do this with this formula - DATEADD({Meeting Slot UTC}, {Hours Difference}, 'hours'). This will adjust the UTC time to reflect the local time based on the hours difference you’ve defined in your table.

Now, to set the reminder time to 8:00 AM on the day of the meeting, you’ll need to extract the date from the converted local time and combine it with 8:00. The formula is -> DATETIME_PARSE(
DATETIME_FORMAT(DATEADD({Meeting Slot UTC}, {Hours Difference}, 'hours'), 'YYYY-MM-DD') & ' 08:00',
'YYYY-MM-DD HH:mm'
)

This creates a date/time field for 8:00 AM local time on the same day as the meeting. Once this is set up, you can use the 8:00 AM reminder field in your automation. Set the trigger condition to check when the reminder field equals NOW() to send your notifications. Just make sure your automation runs frequently enough to catch the exact time.

If you’re still encountering errors like NaN or ERROR, double-check that all referenced fields are populated and correctly formatted. {Meeting Slot UTC} should always be in ISO format with time included, and your {Hours Difference} should accurately reflect the trade show’s local offset.

Hope this will be useful for you!