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

[a3]

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!

[Maskell_Rascal]

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. 

[a3]

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

[a3]

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.

[Maskell_Rascal]

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

[danilang · Accepted solution]

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

Giving the result 

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

Dan

[a3]

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!

[a3]

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. =)