This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
We have financial start and end dates for contracts:
But when I run it through my workflow it gives me this:
this file is a tad different because it says '=H26 for the Financial Contract Start Date, and not just a hard date. But why did it return the date as 1905-07-05 for the start date, but returned the right end date?
I can fix it by making the date a hard coded date in Excel, but I want to understand why it is doing it in this case, and not another.
Your new dates (2013-06-17) are being interpreted as excel internal date types (double) on input by alteryx. The number is truncated to 2013 which converts to 1905-07-05 according to Excel date math. 1899-12-30 is the start date for the Excel date calculations
I've only ever seen this in .xls files before when the Jet engine is used to read data.
What's interesting is that the financial contract end date is converted properly even though it's a formula too. If you look at the Contract start and end date, the source for the Financial values, the start is entered as "2013-06-17" but the end is entered as 06/13/2025. It looks like alteryx is able to convert the dates that have "/" but not "-". The first value in cell H2 is formatted as "03/17/2019" Maybe this is used as a template for column.
Is the conversion something that you do in your workflow, since the data must be brought in as string?
The Excel driver built by Alteryx does some unusual things with field types which has caused issues for me in the past. I agree that the field type is evaluated and changed by the driver to produce these unexpected results.
I was able to produce the desired output here when I tried the "Microsoft Excel Legacy (.xlsx)" driver.
@danilang Yes, I convert it to a date in my workflow. My group has about 130 of these files that are managed by 8 different people, some it was just peculiar it happened on only two of the files. The file formats are less than ideal, but they're legacy files, before I was introduced to Alteryx. What's I guess even weirder, is it did not do it in last months reporting, even though the file was the same.
I didn't see a solution - just explanation for the reason date is being represented in '1905 vs. 201x'. I am not modifying date - i feel like there's a glitch as initially dates were run properly & then i attempted to use the native.csv - 100% are being read in as string. The excel format date fields are all formatted as dates but Altx is reading in/calculating to the conversion you mentioned below. I'll try to do data cleansing/kill whitespace and see if behaving the same. This is completely frustrating!