How to recognize empty Excel cells as DateTime
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Use a select tool to force the data type
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
