Dates in input of model different from xlsx being input

Question

Hi,

I am having an issue with dates in an input to a model. In the original Excel (.xlsx) the date appears as:

Whereas at the input of the model it is reading as:

If anyone has any idea why Alteryx is reading this as the date before? Any help would be much appreciated.

Thanks!

ChrisTX · Answer

Also see these related posts:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Alteryx-misinterprets-date/m-p/386644

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Date-returning-different-number/m-p/387943#M71182

Prevent All Date Conversion on Data Import from Excel

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Prevent-All-Date-Conversion-on-Data-Import-from-Excel/m-p/192758/highlight/false#M49156

This link explains the difference between the two drivers:

https://help.alteryx.com/current/AlteryxDriverXLSX.htm

"Microsoft Excel (*.xlsx)" driver is maintained by Alteryx, as documented in the Help.

I had a similar problem trying to import a column with Time data.

The Excel column with Time data was imported as String, but the data was then converted to Date format

Using "Microsoft Excel (*.xlsx)" driver, the Transaction Time showed the time incorrectly (example: 1900-01-05).
Using "Microsoft Excel Legacy (*.xlsx)" driver, the Transaction Time showed the time correctly (example: 06:16:00).

ChrisTX · Answer

A few options: 1) Try using the Excel Legacy driver. I think the default Excel driver is owned and maintained by Alteryx, while the Legacy driver is from Microsoft. 2) Determine how the data is actually stored inside the XLSX file. Open your XLSX file as a Zip file: see the attached instructions. 3) from https://stackoverflow.com/questions/24497939/openxml-xml-and-excel-worksheet-has-different-values The 't' attribute of the cell element tells you what type of data is in the cell's -element. (Section 18.3.1.4 in the OpenXml-standard). In this case, the 's'-value tells you that the value is an index into the SharedStrings-file. (section 18.18.11 ST_CellType (Cell Type)) of the standard. So at index 48 in the SharedStrings.xml-file you will find the information you need. 4) This information came from Alteryx support: In excel I entered for example 3/9/2020, but Designer shows 2010-0309. Excel stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd. tttttt . This is called a serial date, or serial date-time. The integer portion of the number, ddddd, represents the number of days since 1900-Jan-0. In sheet2.xml it shows: 43899 Alteryx shows Date fields as A 10 character String in "yyyy-mm-dd" format. That is by design. I noticed that in your Input Data tool you are using File Format = Microsoft Excel (*.xlsx). As a workaround you can use File Format = Microsoft Excel Legacy (*.xlsx) and you will see the values like in the Excel file: You can get the Excel Legacy driver from here: https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Output-legacy-Excel-files/td-p/8819 Chris Excel research - View XML data inside Excel - Open as ZIP file - check encoding or formatting errors - spreadsheet structure.txt

OllieClarke · Answer

Hey @furlonla this is weird. I found a stack overflow question with the same issue coming from VBA and Excel: https://stackoverflow.com/questions/36378476/why-does-the-date-returns-31-12-1899-when-1-is-passed-to-it
It looks like in an excel spreadsheet 1900-01-01 is stored as date 1, whereas in VBA (and maybe in the translation into alteryx) 1899-12-31 is stored as date 1. 
Excel apparently deals with this issue by inventing the 29th of February 1900

This date doesn't exist:

Bit more info about it here: https://en.wikipedia.org/wiki/Year_1900_problem

I'm not sure what the best approach is in this case. If you have daily data, then converting dates pre 1900-03-01 by adding a day to them will give you 2 rows with the same date (which might be okay or not depending on what you're doing). If you'd prefer synchronicity between excel and alteryx (and for the 29th of feb to become the 1st of March) then this formula should get you where you need to be:

IF [Effective Date]<todate('1900-03-01') 
THEN DATETIMEADD([Effective Date],1,'day')
ELSE [Effective Date]
ENDIF

Hope that helps,

Ollie