Parsing time string with time zone

[aleaja]

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"
  )
)

[TheTimeSavingCo · Accepted solution]

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!

[aleaja]

Thanks for catching that typo, @TheTimeSavingCo. That was the issue.

[TheTimeSavingCo]

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