My data has dates that I have converted to Alteryx date format.
Some of those dates are 9999-12-31
Whenever the date of 9999-12-31 is found, I want to change it to 2017-12-31, or even better, Todays Date. But either will work for what I need.
I thought a formula tool would do it and wrote the following:
if [Expected_Due_Date] = "9999-12-31" then [Expected_Due_Date] = "2017-12-31" else [Expected_Due_Date] endif
But when I run it, the date returns as 1899-12-30 instead of 2017-12-31?
Can't figure out what I am doing wrong or if there is a better way to accomplish the task.
Thanks,
Larry
Solved! Go to Solution.
try this:
IF [Expected_Due_Date] = '9999-12-31' THEN DateTimeStart() ELSE [Expected_Due_Date] ENDIF
or
IIF([Expected_Due_Date]='9999-12-31',"2017-12-31",[Expected_Due_Date])
or
Switch([Expected_Due_Date],[Expected_Due_Date],'9999-12-31',DateTimeStart())
note the substitution for today's date in examples 1 and 3.
Cheers,
Mark
Thank you Mark, those solutions worked perfect. Appreciate the help.
Larry
Larry,
Glad to lend a hand. BTW, it is national donut day! Have one on me :)