I am trying to make all of my date fields uniform. Most of the date fields are based off of a calculation, but depending on the value, the calculation did not apply, leaving me with two formats in one field (i.e 2016-01-01 and 01/01/16). Additionally, a couple of the date fields are not calculated and are the alteryx default date format. Where this is causing me issues is when I am trying to then do calculations off of these various dates and getting Null values.
Can anyone help me with getting these all to be the same format. DateTimeFormat has not worked for me. I am guessing because of the inconsistency of the dates.
Thanks in advance.
Solved! Go to Solution.
I recommend having a look at this excellent macro (and even better blog post) written by @JPKa ...
I would suggest a formula tool using a Regex_match to determine the format.
IIF(REGEX_Match([Field1],"\d{2}/\d{2}/\d{2}"),DateTimeParse([Field1],"%m/%d/%y"),[Field1])
Thanks for the formula. Looks like everything is working for me now. Not sure if there was any better way to do it, but I put that formula into the tool, once for each way the dates were coming in and then a final formatting formula at the end of it.