Convert to date
- 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
How do I convert the date coloumn to date currently alteryx does not recognize it as such?
RecordID medium country date transactionRevenue transactions
1 000000 email (not set) 20140814 72.422758 19
2 000001 email (not set) 20140821 79.606785 39
3 000002 email (not set) 20141016 72.228595 19
4 000003 email (not set) 20141124 107.566242 19
Thank you.
Mads
Solved! Go to Solution.
- Labels:
- Date Time
- Preparation
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
The solution to this problem is to use the DateTime tool located in the Parse tool box. To use this tool the date field must be set as a string in order for the DateTime tool to convert the field to an actual date. Once that is set, make sure to select "Convert From String Field to Date/Time Field" in the Date/Time tool. The second thing you will want to do is make sure the "Formatted Input String Field" is set to your date field in this case "Date". In the list below select the format that your string data is currently in (your case yyyymmdd). The output of this tool will add a new field to your data that contains the properly formatted Date/Time field that you can now do calculations with.
Thanks,
Paul
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Yes, this is especially applicable to the output of the Google Analytics Connector in Alteryx which outputs for the Date an Integer of 4 formatted as yyyymmdd.
Bring a Select tool after the GA Connector to convert the Date field to a String then the Date time tool to convert the Date field to a format Alteryx will understand as a date...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hello
hope you can help me I am trying to convert Date serial Number to a date IE 42333 = 25 Nov 2015, the cell itself is a string
I have tried using the date convert tool but it did not work
any idea to help me?
many thanks
Denny
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
See this http://www.cpearson.com/excel/datetime.htm and use the dateadd
function in the formula tool.
Could look something like this:
DateTimeAdd('1900-Jan-0', ToNumber([YOUR FIELD]),"days")
Best regards,
--
Daniel Brun
+45 21 25 61 20 <++45+21+25+61+20>
a.Flæsketorvet 68, 1711 København Ve.daniel@inviso.dkt.@DanielPBrun
l.linkedin.com/in/danielbrunw.inviso.dk
Prøv Tableaueller Alteryxgratis i dag
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Sorry - like this.
DateTimeAdd("1900-01-01 00:00:00",ToNumber([your field])-1 ,"days")
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@danielbrun2 beat me to the answer, but I thought I would add a couple of thoughts...
If your field is actually "IE 42333", then the initial step would be to convert that string to an integer using...
ToNumber((Replace([Date], 'IE ', '')))
Also, your date format appears to be how Excel handles dates, with the internal 'serial number' that adds to how many days it has been since the beginning of last century.
If that's the case, you should consider the anomolies around that and a good article on the community is at http://community.alteryx.com/t5/Data-Preparation-Blending/What-date-ranges-are-allowed-issues-readin....
Actually if you use the formula as given (because of the anomolies), you will get '2015-11-26' which is one day off. You may need to adjust your formula by a day (i.e., subtract 2) to get the answer you are wanting.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Currently am having the same situation, however I am receiving the date field as a double - any suggestions on how I might be able to convert this to a date field I can use?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi,
You can use the same formula as mentioned above: DateTimeAdd("1900-01-01 00:00:00",ToNumber([your field])-1 ,"days")
Could you perhaps post 1 date value for reference.
Best,
Daniel
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
The date field looks the same way '20180813' however was coming in with a double data type.
I somewhat had come up with a bit of a solution after I asked and then watched the live training yesterday on date and time parsing. What I ended up doing was converting the field to a string and then used the DateTime parsing tool to convert it into a usable date format.