Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Convert to date

msh
5 - Atom
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
9 REPLIES 9
PaulT
Alteryx Alumni (Retired)
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
Alteryx Alumni (Retired)

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
8 - Asteroid

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
ACE Emeritus
ACE Emeritus
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
ACE Emeritus
ACE Emeritus

Sorry - like this.

 

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

RodL
Alteryx Alumni (Retired)

@danielbrun2 beat me to the answer, but I thought I would add a couple of thoughts... Smiley Happy 

 

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
8 - Asteroid

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
ACE Emeritus
ACE Emeritus

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
8 - Asteroid

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.

Labels