Convert to date

[msh]

Hi,

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

[PaulT · Accepted solution]

Hi!
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

[FredericP]

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

[DennyChan]

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

[danielbrun2]

Hi,

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

[danielbrun2]

Sorry - like this.

DateTimeAdd("1900-01-01 00:00:00",ToNumber([your field])-1 ,"days")

[RodL]

@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.

[jnans]

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?

[danielbrun2]

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

[jnans]

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.