Convert V_WString into Date Format

Question

Hi All,

I got a column named "F3" and the data type that Alteryx shows is "V_WString".

Furthermore, the "F3" Columns contains difference date format, and I would like to convert all of them into yyyy-mm-dd.

I have attached a sample excel file in this post down below. Many thanks!

F331/3/2019
29/12/2018
2014-MAY-15
14/12/2015
2016-FEB-18
2016-MAY-18
2015-JUN-04

Dummy_Data_Transformation.xlsx

alexnajm · Accepted Answer

You can look at creating an IF statement in a Formula tool - DateTimeParse will be your best friend here. Use the specifiers in DateTime Functions (alteryx.com) to help with those statements!

Just make sure your conditions capture all of the date format possibilities - for example in this case, it looks like you can distinguish them by the presence of slashes versus hyphens. But make sure that's always true!

Maskell_Rascal · Accepted Answer

Hi @tamluenwai29 - If the data is consistent and its just the three different formats you've outlined, then I'd recommend using an IF statement with some REGEX_Match statements to parse it in one formula. The below formula will convert everything you've outlined in your question.

IF REGEX_Match([F3], '\d{4}-[A-Z]{3}-\d{2}') = -1 
THEN DateTimeParse([F3],'%Y-%b-%d') 
ELSEIF REGEX_Match([F3], '.*\/\d{4}\b') = -1 
THEN DateTimeParse([F3],'%d/%m/%Y') 
ELSE DateTimeParse([F3],'%Y-%m-%d') 
ENDIF

AlteryxTrev · Accepted Answer

@Maskell_Rascal has a great solution is you like Regex. I like to close my eyes when regex happens to pass my visual path so I'll piggyback off of his solution with this formula. If you accept this solution, please accept his, too :)

IF
Contains([F3], "-") THEN DateTimeParse([F3],'%Y-%b-%d') ELSEIF 
Contains([F3], "/") THEN DateTimeParse([F3],'%d/%m/%Y') ELSE 
DateTimeParse([F3],'%Y-%m-%d') ENDIF