community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Date returning different number

Does anyone know what would cause this to happen: 

 

We have financial start and end dates for contracts: 

Excel Project Model.png

 

But when I run it through my workflow it gives me this:

 

Alteryx Output.png

Alteryx Metadata.png

 

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. 

 

Thanks, 

Rebecca

Alteryx Certified Partner

Hi @RebeccaHennessy.

 

That's definitely a curious issue. Could you attach an Excel with just the relevant cells tor us to reproduce this for testing?

Hi @CharlieS , 

 

I've attached one. Even when I threw that in a sample workflow, it still returned that: 

 

Sample Sheet.png

 

I'm stumped (easy to do, but still :) )

Pulsar

Hi @RebeccaHennessy 

 

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 

 

Date.png

 

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?

 

Dan

 

 

Alteryx Certified Partner

Excellent investigation work, @danilang.

 

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 appreciate all your explanations and digging!

 

 

Labels