Use calculated Time Zone in Formula

[Benjamin_Pantil]

Hello I am trying to create an interview tracker.
I have the following fields:
Interview ID - formula
Candidate - lookup
Client - lookup
Interview Date - Date, US format with 12 Hour time, Using GMT for all collaborators.
Interview Time Zone - Single Select - Options such as “Pacific”, “Central”
Calculated Time Zone - Formula

The Calculated Time Zone field takes the inputted time zone and outputs the supported timezone format for Set_TimeZone using the formula:

IF({Interview Time Zone} = “Pacific”, “‘America/Los_Angeles’”, IF({Interview Time Zone} = “Mountain”, “'America/Denver’”, IF({Interview Time Zone} = “Central”, “'America/Chicago’”, IF({Interview Time Zone} = “Eastern”, “'America/New_York’”, IF({Interview Time Zone} = “GMT”, “'GMT’”, “Error”)))))
Field displays:‘America/Denver’ or ‘America/Los_Angeles’

The Interview ID field takes all the info and creates a unique ID based on the time zone the interview is in. The formula it uses is:

CONCATENATE(Candidates, " - ", Client, " - ", {Interview Type}, " - ", DATETIME_FORMAT(SET_TIMEZONE({Interview Date},{Calculated Time Zone}), ‘ll LT’))

The time that is displaying in the Interview ID field is not working properly, no matter what I do. Based on the above, it is currently displaying the same time that is in the Interview Date field.

[Justin_Barrett · Accepted solution]

Welcome to the community, @Benjamin_Pantilat! :grinning_face_with_big_eyes: At first I couldn’t see any problem with your time zone switching formula (other than a slight inefficiency; more on that later). However, upon closer inspection, it appears that you’ve got extra single quotes inside the timezone indicator strings. For example the first part of your formula is currently this:

IF({Interview Time Zone} = "Pacific", "'America/Los_Angeles'", ...

…when it should be:

IF({Interview Time Zone} = "Pacific", "America/Los_Angeles", ...

Remove all of those inner single quotes and the time zones should calculate properly.

On the efficiency side, that formula could be simplified and shrunk by using SWITCH() instead of nested IF() functions:

SWITCH({Interview Time Zone},
    "Pacific", "America/Los_Angeles",
    "Mountain", "America/Denver",
    "Central", "America/Chicago",
    "Eastern", "America/New_York",
    "GMT", "GMT",
    "Error"
)

SWITCH() is ideal for times when you’re looking at the value of a single field, and switching the output based on what you find there.