Parsing date from text into an exact Date with specific timezone

[Karl_at_Easy_La]

I would like to parse a date string of "2023-02-10", with no time information, into a specific timezone (Europe/Malta). We use daylight savings, so right now, 2023-02-10 00:00 Malta time would be 2023-02-09 22:00 GMT.  For this reason, I cannot specifically add a timezone of +2 because this will chagne to +1 later in October. 

I noticed DATETIME_PARSE() takes a locale as the 3rd parameter. However, this does not seem to be documented at all in the formula reference. I've tried using values like "Europe/Malta", which works in SET_TIMEZONE(), but doesn't do anything.  I've also experimented with SET_TIMEZONE(), but that seems the other way round, to convert an existing Airtable date with a particular timezone.

Basically, what I would like is something where for example could parse "2023-02-10" into the Malta timezone. 

[David_Skinner]

You can use the DATEADD() function to offset the number of 'hours'

[Karl_at_Easy_La]

That wouldn't work, as the timezone hours changes from +1 to +2 depending on daylight saving.  

[David_Skinner]

Can you not just set the Timezone in Formatting to Europe/Malta then?

[Karl_at_Easy_La]

No, as if you parse a date of 2023-02-10, and then show it in Malta time, it shows as 2023-02-09 22:00, as the parsing does it in GMT. Also, I do not want to hardcode it to Malta - that was to give an example.  

The use case is that I have bookings coming in from a channel manager, who have a checkin & checkout date. There is no concept of timezones, however based on the property location, I want to offset the checkin to be the timezone of the place. 

For example, a checkin date in Malta of 23/09/2023 would mean 22/09/2023 22:00 in GMT. A checkin in Argentina of same checkin 23/09/2023 would be 23/09/2023 04:00 in GMT.

[ScottWorld · Accepted solution]

Airtable is not the best platform to work with if you’re dealing with anything time zone related (and especially if you’re dealing with multiple time zones), but my sample time zone base & training video in the Airtable Universe is the most comprehensive time zone resource that is currently available for Airtable:

https://www.airtable.com/universe/expL1TuVyaYaInqm2/working-with-multiple-different-time-zones-in-ai...

p.s. If you have a budget for your project and you’d like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld

[Karl_at_Easy_La]

That's very similar to what I'm looking for.  I was just going to post a very similar solution as I figured out there is no native way.  I was using more columns and extracting the HOUR from the timezone, and doing some calculations.

Your solution is definitely way cleaner than what I came up with, thanks for your answer!

[ScottWorld]

You’re welcome! Glad I could help! 😀