Date Conversion

[johnrexrima_19]

Hi all,

Can anyone help me in converting to (MM/dd/yyyy) date format? In my input CSV file the date fields are in custom date format (See screenshot) however it is being fed in Alteryx in number format in the results preview (See screenshot) but categorized as V_Wstring by the Select tool. I cannot easily change the data type to Date using the Select tool as I keep getting errors that some of it are not a valid date. I also tried using the Formula, Multi-Field Formula and DateTime tool but got the same error that some fields are not a valid date. Thank you!

[RolandSchubert]

Hi @johnrexrima_19 ,

the date seems to be stored in the Excel internal format (days since 2019/01/01), so you could try converting it using a formula:

DateTimeAdd("1900-01-01", [DateField], "days").

Best regards

Roland

[johnrexrima_19]

Hi @RolandSchubert

Thank you for your suggestion. I tried your formula but I got an error saying that "Argument 2 of DATETIMEADD is not a number".

Best,

Rex

[OllieClarke · Accepted solution]

Hi @johnrexrima_19 
If you change your date fields to an in32 using a select tool, then @RolandSchubert's formula will work. Alternatively you could convert them to numbers in the formula like so:

Datetimeadd("1900-01-01",Tonumber([Date]),"days")

[RolandSchubert · Accepted solution]

I think, a conversion to a number would solve this:
DateTimeAdd("1900-01-01", ToNumber([DateField]), "days")

[johnrexrima_19]

You guys are awesome! Thank you both for your help!