Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Date returning different number

RCurry1230
7 - Meteor

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

7 REPLIES 7
CharlieS
17 - Castor
17 - Castor

Hi @RCurry1230.

 

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

RCurry1230
7 - Meteor

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 :) )

danilang
19 - Altair
19 - Altair

Hi @RCurry1230 

 

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

 

 

CharlieS
17 - Castor
17 - Castor

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.

RCurry1230
7 - Meteor

@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!

 

 

hcampbe1
5 - Atom

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!

ntobon
Alteryx
Alteryx

File appeared to be a .Xlsx, but when I looked closer - it was saved as a "Strict Open XML Spreadsheet" rather than "Excel workbook", both have the .xlsx extension.

 

I opened the file, then File >Save As, and select 'Excel Workbook (*.xlsx). With the new file AND with File Format "Microsoft Excel Legacy (.xlsx)" I see the correct dates in Designer. 

Labels