Dates from Alteryx into Excel - Excel reads as text - Special Characters?

[wiggot]

I've got a date field in Alteryx. I've converted it from the default to "MM/dd/yyyy" for output into Excel. In the SELECT tool, I have to choose "String" as the data type when I do this. (I've tried using the default date format and Date data type, and it makes no difference to the issue that follows). I use a Basic Table & Render to create the output file.

The problem I'm having is that Excel sees the date column as text, and the only way that I've been able to get it to recognize the date as a Date is to go into the field and backspace BEFORE the "7" (for July). Or, go in and manually retype the date in each row. Neither option is acceptable. Doing a global change on the column to type "Short Date" does NOTHING. CHAR(52) is 4, CHAR(55) is 7. 

Non-Alteryx Date	FALSE		52		43315
	ISTEXT	CLEAN	CODE	REPLACE	TRIM	SUBSTITUTE
Alteryx Manually Retyped	FALSE	43300	52		43300
Alteryx Date	TRUE		55			7/19/2018

This is an issue because a later column compares the Log Date (or date the change was made in our system) against the Date Due. If the Log Date is before the Date Due, then the work was done timely. It's a metrics thing for the bean counters. At this point, EVERY date coming out of Alteryx is considered not timely, because Excel doesn't see it as a Date.

Has anybody run into this before or can anybody think of some automated way that I can get Excel to recognize the output as a Date instead of text?

If I can solve this, then I could probably also solve a similar issue... I can have Alteryx "write" the formula that would go into an Excel field, but when it gets to Excel it's read as text and not a formula. Same thing... if I go in and backspace before the 1st character, then Excel reads it as a formula.

[wiggot]

And, just in case anybody asks...the Output Data Tool produces the same problem.

[BenMoss · Accepted solution]

When Alteryx renders an excel file it passes it's metadata into Excel, there for converting it from a date to a string is forcing the file to write it as a text field.

You can confirm this by not converting it to text and then using a standard output data tool.

I'm not sure what the alternatives are for avoiding the problem of the conversion to a specific format though.

Ben

[BenMoss]

You could probably write some sort of VBA script which is triggered to run after the workflow has completed to perform the conversion of the date format into that needed for the report.1

There may be simpler methods mind.

[agentzerow]

If the date is formatted as yyyy-MM-dd in Alteryx and the date format is 'Date', when you output to Excel this will show up as dd/MM/yyyy or MM/dd/yyyy as you need it. If you are doing a match in Alteryx, it might be better to convert the other date to yyyy-MM-dd and have the date match based on that.

[wiggot]

This is actually the solution... leave the date as it is in Alteryx and only output using the Output Data Tool. The Render Tool creates the problem. I had tried that Tool initially, but I think I had already set the Log Date to String, so it wasn't working.

This works because we've got to copy the output data into another file anyways. Otherwise, we'd have a problem because we prefer to use the Render Tool since the Output Data Tool produces hideous spreadsheets.

[mevans7]

I just converted the excel file to a csv and problem was resolved...