Date Conversion
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
Solved! Go to Solution.
- Labels:
- Date Time
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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")
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I think, a conversion to a number would solve this:
DateTimeAdd("1900-01-01", ToNumber([DateField]), "days")
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
You guys are awesome! Thank you both for your help!
