Timezones and V_String

[Melibau]

Hi!

I have an Excel file that downloads to CDT with no spaces (e.g. 10:30am) and need to change it to EDT.

In Excel I use this formula: =SUBSTITUTE(SUBSTITUTE([Cell],"am"," am"),"pm"," pm")+1/24

In Alteryx, I've gotten as far as using the multi-field formula tool:
REPLACE(REPLACE[COLUMN],"am"," am"),"pm"," pm")

(Example output = 9:30 am) I still need to change it to EST, but I keep getting nulls when I've tried different formulas/tools.

The type is V_String, but it doesn't work if I change the type using the Select tool either.

E.g.
Report downloads as 10:30am CDT
Need it to show 11:30 am EDT

Thanks in advance!

[T_Willins · Accepted solution]

Hi @Melibau,

The difficulty you are running into is manipulating a combination of both sting and numeric data in a single field.  It may be easier to break the string into it's components using RegEx, manipulate it, then bring it back together.  The attached workflow does this and takes into consideration if am needs to be changed to pm (and vice versa) with the change in time zone.  In theory this could all be done using a single formula tool and a single formula using formulas such as Left, TrimLeft, Right, Trim Right, ToNumber, To String, and so on, it is easier to create separate fields and recombine them.  The time could also be converted to a DateTime type, add an hour, then reconvert to the desired string, but it is more complex than this workflow.

[Qiu]

I am not sure that simple substitute can deal with 12PM issue.
Anyway, I refer to the post below and made some modifications to suite your request, if correctly.

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Convert-Text-quot-date-time-am-pm-quot...

The pure Time format gives me warning, so I have to add a dummy Date.

Please do share if someone has a better idea.

Spoiler