Save the date! Join us on October 16 for our Product Ops launch event. Register here.
May 02, 2023 07:12 AM
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"
)
)
Solved! Go to Solution.
May 03, 2023 12:39 AM
Hm, the expected format is `America/Los_Angeles`, not `Americas/Los_Angeles`. Could you try updating that bit and seeing whether that helps?
If you're still facing issues if you could provide access to an example base with the formulas and fields set up it would be really helpful in assisting you with this!
May 03, 2023 12:39 AM
Hm, the expected format is `America/Los_Angeles`, not `Americas/Los_Angeles`. Could you try updating that bit and seeing whether that helps?
If you're still facing issues if you could provide access to an example base with the formulas and fields set up it would be really helpful in assisting you with this!
May 03, 2023 06:48 AM
Thanks for catching that typo, @TheTimeSavingCo. That was the issue.
May 03, 2023 11:19 PM
Glad I could help. Kinda wish it'd throw an error for that kind of thing instead of just "working" and leaving us puzzled sigh