I have a table that includes a date field as well as a single select field with options that are times of day (e.g., 9:00am, 12:00pm, 3:00pm). This allows users to select a date and time from a limited set of time options rather than Airtable's unrestricted datetime selector.
I'd like to compose these fields into a single, calculated datetime field. This composition is a fairly elementary task, but the formula keeps parsing the time string in UTC. How can I get Airtable to read the time string based on another time zone (i.e., the time zone my team works in)?
Here's the formula I've written so far. I've tried using `SET_TIMEZONE()` within the various `DATETIME_FORMAT()` functions, but can't quite get the formula output right:
IF(NOT({Time option}),
{Date},
DATETIME_PARSE(
DATETIME_FORMAT(
{Date},
"YYYY-MM-DD"
)&" "
&DATETIME_FORMAT(
SET_TIMEZONE(
DATETIME_PARSE(
DATETIME_FORMAT(
NOW(),
"YYYY-MM-DD"
)
&" "
&{Time option}
&DATETIME_FORMAT(
SET_TIMEZONE(NOW(),"Americas/Los_Angeles"),
"ZZ"
),
"YYYY-MM-DD h:mmaZZ"
),
"Americas/Los_Angeles"
),
"hh:mmZZ"
),
"YYYY-MM-DD hh:mmZZ"
)
)