Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
SOLVED

In Excel 04/15/2019 but alteryx change it to 43570

Highlighted
6 - Meteoroid

my source date is from excel and loaded it to Alteryx.  My date was 04/15/2019 but it become a string value of 43570 and cannot convert using the tool to date format. Alteryx says cannot convert "43570" to a date/time format.  Please help!

Highlighted
9 - Comet

Use the ToDate formula in the Formula tool to convert a string to a date. You can then change it over to a Date vs a String with the Select tool. 

Highlighted
6 - Meteoroid

i tried your solution todate([MyDate]) but still giving me a null, for the 43570 value

Highlighted
6 - Meteoroid

i tried your solution todate([MyDate]) but still giving me a null, for the 43570 value

 

so i don't know, how the 04/15/2019 date gets converted by alteryx to 43570 and now that number cannot be transformed.

Highlighted
9 - Comet

Is 43570 stored as a String or a Double? It needs to be a double for the formula to work. 

Highlighted
17 - Castor
17 - Castor

Hi @a3 

 

Excel stores dates internally as offsets from 1899-12-30, so 43570 = 2019-04-15.  Sometimes, usually with .xls, but some times with .xlsx files, Alteryx interprets the date as a number, or in your case a string.  To convert it back to a date field use the following in a formula tool

 

 

ToDate(ToNumber([DateAsNumber]))

 

 

@Maskell_Rascal was correct in the conversion, but the field has to be a number.  The "ToNumber" in the formula converts the string to a number.  It also works with fields that are already numeric. 

 

The attached WF converts 43570 into the Date field 2019-04-15

 

 

WF.png

Giving the result 

 

Result.png

 

Note: Alteryx stores all dates internally in the format YYYY-MM-DD

 

Dan

Highlighted
6 - Meteoroid

THanks danilang!  That works and fixes my date.

 

But now a new issue comes up, the blank fields are now showing up a date as "1899-12-30".  It should be null.  What do i do?

 

Thanks again!

Highlighted
6 - Meteoroid

I was able to figure out how to fix the date issue

 

IF [MyDate] = ""
THEN [MyDate]
ELSE ToDate(ToNumber([MyDate]))
ENDIF

 

then i don't get anymore the 1889-12-30 year. =)

Labels