Multiple monthdateyear format conversion to Alteryx YYYY-MM-DD format

[virgosquirrel]

Hello, 

I've researched a few current thread about date format but I haven't able to resolve my date conversion to Alteryx format. 

My date column has different format such as: YYYY-MM-DD, mm/dd/yyyy, m/dd/yyyy, m/d/yyyy (with each letter represent 1 number, 2 letter represent 2 numbers)  and also blank. The one with the correct Alteryx format is YYYY-MM-DD I don't want to touch that. 

I used the Multi field formula to populate the Regex and Datetimeparse to convert my date format but it gave me the error (see attached screenshot) 

Can someone please help how I correct the formula below to get the conversion run? 

IF REGEX_Match([Hire Date], '(\d{2}/\d{2}/\d{4})') THEN

DateTimeParse([Hire Date],"%Y/%m/%d")

ELSEIF REGEX_Match([Hire Date], '(\d{4}/\d{2}/\d{2})') THEN

DateTimeParse([Hire Date],"%Y/%m/%d")

ELSEIF REGEX_Match([Hire Date], '(\d{4}-\d{2}-\d{2})') THEN

DateTimeParse([Hire Date],"%Y-%m-%d")

ELSEIF REGEX_Match([Hire Date], '(\d{2}-\w+-\d{4})') THEN

DateTimeParse([Hire Date],"%Y/%m/%d")

ELSEIF REGEX_Match([Hire Date], '(\d{4}/\d{2}/\d{2})') THEN

DateTimeParse([Hire Date],"%Y/%m/%d")

ELSE

NULL()

ENDIF

[alexnajm]

Your first condition seems odd - you are saying if the regex is \d{2}/\d{2}/\d{4} then convert via DateTimeParse([Hire Date],"%Y/%m/%d")? I think this is not correct

Seems like a similar issue for your 4th conversion! Check your Formula and see if you can fix these inconsistencies

[virgosquirrel]

Thanks , can you suggest what is the correct way for the regex formula ? 

[alexnajm · Accepted solution]

@virgosquirrel if you built this Formula, I am sure you can edit it to work! For example, you are saying %Y/%m/%d needs to match the pattern \d{2}/\d{2}/\d{4}? No, most likely it should be \d{4}/\d{2}/\d{2}

You got it - just work through the steps!

[virgosquirrel]

I got it to work, thank you!

[Qiu]

@virgosquirrel 
It looks like you need to check this weekly challenge out.

https://community.alteryx.com/t5/Weekly-Challenges/Challenge-414-Mastering-Date-Formats/td-p/1242461

[virgosquirrel]

Thank you ! Will do!