Convert dates with different date format into single format

[D_Y]

I have a field that has dates that come in different formats:

Field

06/01/2023

2023-12-23

Is there a way to convert the first example in this field into the Alteryx-supported ISO date format, while keeping the second example because it's already ISO? We can assume that it in the first example, it is always mm-dd-yyyy (June 1st, 2023).

[binuacs]

@D_Y One way of doing this

[D_Y]

Hi @binuacs 

Sorry to mention, sometimes it is also 06-01-2023. But thank you so far for your help!

[ChrisWaspe]

Hi @D_Y 

There are a few options you could use and decide which suites best. Essentially it is an IF statement to apply the necessary conversion function in the formula tool. 
The first is to use regex to test if the format matches the first row, in that case use the DateTimeParse function to convert the string, ELSE use the ToDate function to convert the string. 

"IF REGEX_Match([String], '\d\d\/\d\d\/\d\d\d\d')
THEN DateTimeParse([String],'%m/%d/%Y')
ELSE ToDate([String]) ENDIF"
The other option is to try the ToDate function, and if that fails (returns Null), then use the DateTimeParse function to convert the string, ELSE use the ToDate function.

"IF IsNull(ToDate([String]))
THEN DateTimeParse([String],'%m/%d/%Y')
ELSE ToDate([String]) ENDIF"

Workflow is attached. Please let me know if you have any other questions.

[binuacs · Accepted solution]

@D_Y updated workflow attached

[D_Y]

Thank you @ChrisWaspe @binuacs for your help!

[someotherguy]

@ChrisWaspe  what if you have several different formats?

dd/mm/yyy
m/d/yyyy
m/dd/yyyy
mm-dd-yyyy
mm/d/yyyy
mm/dd/yy
mm/dd/yyyy
yymm-dd-yy
yymmdd
yyyy/mm/dd
yyyymmdd