Want to replace date with another date

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

Expression

Accepted answers

try this:

IF
   [Expected_Due_Date] = '9999-12-31'   THEN DateTimeStart()
ELSE
   [Expected_Due_Date]
ENDIF

IIF([Expected_Due_Date]='9999-12-31',"2017-12-31",[Expected_Due_Date])

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

try this:

IF
   [Expected_Due_Date] = '9999-12-31'   THEN DateTimeStart()
ELSE
   [Expected_Due_Date]
ENDIF

IIF([Expected_Due_Date]='9999-12-31',"2017-12-31",[Expected_Due_Date])

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