Covert different time zone to local time

[Jennyman130]

Hi all,

I would like to change the  date and time of various time zones in the data for performing the review easily, for example: 11/3/2017 4:15 PM AWST. The AWST here stands for Australian Western Standard Time, and I would like to covert it to local time - Hong Kong time. Is it possible please?

Many thanks in advance,

Jenny

[Jennyman130]

Many thanks! I can see it now.

However, my data includes the timezone SST/CET/GMT/EST which I have updated into the workflow below. And the local time depends on which time zone it is. In this case, do I need to split out the timezone into a different column first to get the formula work? And do I need to use If formula to make the formula work for each timezone?

Thanks,

jenny

[Jennyman130 · Accepted solution]

Sorry if my explanation in the previous post was a bit confusing. But the final output I want is a separate column which shows the Hong Kong local time based on the data attached above.

I’m thinking to first use a regex parse function to split out the timezone, then use a if formula along with datetimeadd() function to get the local time. Does anyone know if this is the solution or there is an easier way to do it please?

Many thanks,
Jenny

[StephenR · Accepted solution]

It's probably simplest to break out the time zone into another field and use something like:

IF [Timezone] = "EST" THEN

DateTimeAdd([Datetime], 12, "hours"

ELSEIF [Timezone] = "other time" THEN

DateTimeAdd([Datetime], x, "hours"

...

...

...

ELSE

[DateTime]

ENDIF

[BenMoss · Accepted solution]

I'd use a lookup table to join the offset data, this way it can be managed more effectively. Once you have the table, join on the common field (timezone) and then use the additional offset field in your datetimeadd() calculation.

Ben

[Jennyman130]

Thanks for the great ideas!