Covert different time zone to local time

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

Preparation

Accepted answers

Jennyman130

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

StephenRuhl

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

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

All comments

StephenRuhl

Alteryx only has UTC to local datetime conversions built in, but you can use DateTimeAdd() to adjust for the difference in hours between known time zones. However, in your case Hong Kong is in the same time zone as AWST so this shouldn't be necessary.

Jennyman130

Thanks Sruhl, good to know that there is way to get that work.

Is DateTimeAdd() a function in Formula or it's within Date/Time parse function? Would you mind to show me in work flow on how to use this function for my data format?

Many thanks,

Jenny

StephenRuhl

Sure thing @Jennyman130. Please see the attached workflow.

DateTimeSample.yxmd

Jennyman130

Thanks so much all, but I got an error message saying the sample is a newer version of Alteryx, so I can't view it.

Is it possible to post as a screenshot please?

Many thanks,

Jenny

BenMoss

Handy trick.

You can downgrade a workflow by opening it up in a text editor and amending the version (outlined in line 2), to 11.5 for instance.

Ben

Jennyman130

Sorry I tried to open it with a notepad, but the second line is like below. Doesn't seem quite like the format you mentioned.

andreea_aurica14

Hello,

In formula tool you can use function DateTimeAdd([field],x,"Hours") where x is the time zone difference.

time_zone.yxmd

BenMoss

right yes, so you can simply overwrite 2018.1 to 11.5 or the correct version number and then save it.

Then you will be able to open the workflow.

ben

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

DateTimeSample.yxmd

Jennyman130

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703