Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Parsing time string with time zone

Topic Labels: Dates & Timezones Formulas
Solved
Jump to Solution
1419 3
cancel
Showing results for 
Search instead for 
Did you mean: 
aleaja
6 - Interface Innovator
6 - Interface Innovator

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

 

1 Solution

Accepted Solutions
TheTimeSavingCo
18 - Pluto
18 - Pluto

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!

See Solution in Thread

3 Replies 3
TheTimeSavingCo
18 - Pluto
18 - Pluto

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
6 - Interface Innovator
6 - Interface Innovator

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

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