How to recognize empty Excel cells as DateTime

[1737280]

I have two columns in an Excel file like this:

Start Date	End Date
1/1/2010
3/15/2010
12/1/2010

"Start Date" column comes in as I expected.  However, in Select Tool, I can see that "End Date" is treated as Double data type.  If I put a DateTime Tool or Formular Tool with forumula DateTimeParse([End Date], "%M/%d/%y"), they all result in error:

Formula (16) Parse Error at char(0): Type mismatch. Number provided where a string is required. (Expression #5)

I need "End Date" to be recognized as DateTime type as other files may have actual values in the column.  How can I do that?

[Hugo]

Use a select tool to force the data type

[1737280]

I tried that, but the Type dropdown in Select Tool configuration panel does not have Date, Time, DateTime as options, because Alteryx thinks it's a Double type.  The input has to be a string type in order for Date to be an option.

[fmvizcaino · Accepted solution]

Hi @1737280 ,

I think this is what @Hugo meant. Use a select tool to change the format first, then use your formula tool to parse anything.

Best,

Fernando Vizcaino

[1737280]

Thank you @fmvizcaino @Hugo   I got it working now.  A Select Tool in front of the Formula Tool to change the Type from Double to V_String first, then use the DateTimeParse() in the Formula Tool, then a second Select Tool to set the column to DateTime type.

[dimsandwich]

Old thread but another option would be to take the flow attached above and add dates in the second column of the input tool and run.

You will then see the "Date: Forced" option appear in the Select tool for both fields.

Pick "Date: Forced" in the select and save.

Then you can remove all the dates from the input tool and run again and the Select tool forces the input fields to be treated as dates.

I can't attach an example sadly due to my companies restrictions so hopefully my words are clear enough.