how to convert 1/0/1900 date to 0

[kamranyousaf]

Hi, I am a new user of Alteryx and having issue with the data output. if there is blank field in date field then I get the output as '1/0/1900' and I would like to replace it with 0. I'm getting the "invalid type in operator ==" error for the following formula. I'm not sure what isn't correct about the way it's written.

if ([_CurrentField_]) = "01/00/1900" then 0 ELSE ([_CurrentField_]) endif

i am using multi field formula since i have more than 1 field having date with the same issue. 

[christine_assaad]

Hi @kamranyousaf 

The data in the sample workflow you sent was in YYYY-MM-DD format. If you want to have it in mm/dd/yyyy format, this is doable. You will need to change the criteria to "12/30/1899" just to match the fields format.

[kamranyousaf]

Christine,

sorry to bug you again. please ignore my previous sample workflow file.

Let me give you a bit more background about my workflow. I am joining multiple files and if there is no date for a given unique identifier, alteryx adds a blank cell in the date field that is not formatted as date. And I want to replace it with 0 but want to keep my dates as it is. I attach sample of my output file.

You will notice that dates in output field are formatted as 'date' whereas blanks are formatted as 'general'.

[christine_assaad · Accepted solution]

Hi @kamranyousaf 

0 is not a Date value. If you are done with your calculations and it's just a formatting issue, I would change the "Date" type to "String" (you can format it the way you want) and then replace blank with zero.

Hope this helps. Cheers!

[kamranyousaf]

Thank you so much ! 🙂