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 |
Solved! Go to 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
Thank you Jonathan! That worked perfectly, must get some more practice with RegEx formulas, cheers 🙂