Rounding DateTime error when Importing Excel

[ctpiccone]

I have a date field that when viewed in excel, shows up as "1/1/1900", but when it is imported to alteryx, it is imported as a DateTime, and due to rounding errors, appears as, "1899-12-31 00:00:00". I don't see an option in the input tool, but I believe that it is due to importing the field as a DateTime, then trying to convert from a date field to datetime. Please let me know of any suggestions!

[CKP]

Hi @ctpiccone 

In excel a value of 1 will show as 1/1/1900 when converted to date format, because 1/1/1900 is the first date of the Excel calendar. My guess is that the first date in the Alteryx calendar is 31/12/1899. Either way the value should still be one.

Looks like this is quite an old issue:

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/What-date-ranges-are-allowed-issues-re...

A work around would be to simply add a formula tool after your input and add 1 to the date field. 

[CKP · Accepted solution]

Hi @ctpiccone 

I've been doing some digging around. It seems Microsoft Excel counts 1900 as being a leap year when it wasn't. So to work around the problem you'll need remove 28/02/1900 (the 60th day in the calendar) and add 1 to each date prior to 28/02/1900, as per below:

Let me know if this solves your problem.