Dates in year 1900 are shifting to the left

[mike_r_johnston]

I've noticed an issue with dates from 1/1/1900 to 2/29/1900 from an Excel file input where Alteryx shifts it to the previous day, as though it translates "1" to be 12/31/1899 (it's translated to 1/1/1900 in Excel). We use 1/1/1900 as a placeholder value in our DBs, so it needs to match when doing audits. Any idea why this happens or easy fixes? 

I've attached an input file and workflow to demonstrate. The left column and right column are the same dates, but added an apostrophe to the right to view as text.

Thanks!

[SPetrie · Accepted solution]

Easiest fix is to check for that specific date range and to manually adjust the dates as necessary.

Thats a hold over from Lotus 1-2-3 compatibility in Excel.

2/29/1900 isnt a real date since 1900 wasnt a leap year, but it was in Lotus for some reason. 

From my understanding, it was already integrated into so many things that fixing it would have caused more issues than it fixed.

What you are seeing the the offset between Alteryx's date system and that wonky compatibility system with the extra day. That's also why they start to line up after 2/29/1900.

[mike_r_johnston]

Thank you! That's very helpful (and interesting). I just learned a lot about the leap year problem and the LY system, so double thanks!