Convert V_WString into Date Format

[tamluenwai29]

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!

F3

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

[alexnajm]

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]

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

[trevorwightman]

@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

[tamluenwai29]

Thank you so much! It works perfectly