Alteryx misinterprets date

Question

Hello everyone,

Something has changed in my regular input file (left) and as a result - Alteryx now misinterprets date field and displays data incorrectly (right):

Perhaps you've had similar problem happen to you recently?

I've managed to temporarily fix this problem by converting dates to strings in Excel and changing dots to dashes, however it's quite tedious work.

Thank you and looking forward!

danilang · Accepted Answer

Hi @augustinasn87

Am I correct in assuming that these are .xls files?  Your new dates (2019.02.01) are being interpreted as excel internal date types (double) on input by alteryx.  The number is truncated to 2019.02 which converts to 1905-07-11 according to Excel date math.  1899-12-30 is the start date for the Excel date calculations

The formula tool uses the following formula to convert from excel internal date to formatted date

DateTimeAdd([start],[Field1],"days")

I've only seen this with .xls files.  I think it's the Jet engine that does the conversion and returns a date field.

Can you possibly convert the file to .xlsx. That should stop the conversion and the fields will be returned as 2019.02.01. You'll need to parse the date time of course, but you should get the right result.

Dan