Alteryx misinterprets date

[augustinasn87]

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 solution]

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

[augustinasn87]

Danilang,

Thank you for your response.

File appeared to be a .Xlsx, but when I looked closer - it was saved as a "Strict Open XML Spreadsheet" rather than "Excel workbook", both have the .xlsx extension, so it's a honest mistake from client's side.

I've saved the file as a proper Xlsx and it works flawlessly.

Thank you very much!