Convert dates in multiple string formats to one consistent date format

[Katie_K]

Hi folks, I have found lots of threads and help on converting dates, but I'm wondering if there's an easy way to convert a column with dates in multiple different formats (but all set as string values) into one consistent date format?  I was thinking a formula tool that says something like:

If format of the date column is mm/dd/yyyy then %Y-%m-%d

ElseIf format is m/d/yy then %Y-%m-%d

ElseIf format is dd-mm-yy then %Y-%m-%d

ElseIf format is yyyy/mm/dd then %Y-%m-%d

Else if.... etc. etc. for each format the dates are in

...but there are a lot of different formats so just wondering if there's a more efficient/better way than examining the column for every layout possible and making an "else if' for it?  I haven't really used the RegEx tool, but maybe it can help here - ?

Sample of potential date formats below, thanks!

DateofHire
10/27/2008
01-06-2014
9/29/2014
2/16/2015
05-01-2015
9/26/2011
8/15/2011
8/18/2014
2/20/2012
8/18/2014
2011-03-07
07-07-2014
05-12-2014
03-07-2011
4/30/2012
9/29/2014
01-05-2015
9/27/2010
07-05-2011
03-05-2012
9/29/2014
01-10-2011
09-06-2011
2/16/2015

[Jonathan-Sherman · Accepted solution]

Hi @Katie_K,

You could use:

IF REGEX_Match([DateofHire], '\d+/\d+/\d+')
THEN DateTimeParse([DateofHire],'%m/%d/%Y')
ELSEIF REGEX_Match([DateofHire], '\d{4}-\d+-\d+')
THEN DateTimeParse([DateofHire], '%Y-%m-%d')
ELSEIF REGEX_Match([DateofHire], '\d+-\d+-\d+')
THEN DateTimeParse([DateofHire], '%d-%m-%Y')
ELSEIF REGEX_Match([DateofHire], '\d{4}/\d+/\d+')
THEN DateTimeParse([DateofHire], '%Y/%m/%d')
ELSE '' ENDIF

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[Katie_K]

Thank you Jonathan!  That worked perfectly, must get some more practice with RegEx formulas, cheers 🙂