Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

How to recognize empty Excel cells as DateTime

8 - Asteroid

I have two columns in an Excel file like this:

Start DateEnd Date


"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?



9 - Comet

Use a select tool to force the data type

8 - Asteroid

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.



17 - Castor
17 - Castor

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.





Fernando Vizcaino

8 - Asteroid

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.



5 - Atom

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.