Hi everyone,
I have a doubt with one issue that I found several times and I don't know yet how to solve it in the best and fastest way. I have a column with Dates but in a string format and the values are "Jan-2015", "Feb-2015", "Mar-2015", etc. And I want to change it to a Date field.
My first idea was to create a formula using Replace. And replace Jan for 01, Feb for 02, etc. and after that use the DateTime tool. My doubt is: why I can't use a single formula statement like replace([String],"Jan","01") and replace([String,"Feb","02") ... and I have to use different statements for each replacement? There's a faster way to do it?
Thanks!
Pablo
Solved! Go to Solution.
Thanks to everyone!
I have try all your proposals and all work perfectly. That's what I love of Alteryx: One problem and a huge number of possible solutions :-)
Thanks again,
Pablo
Nice work @s_pichaipillai I like this approach. The find replace tool is a perfect tool for this. I'm not sure why you are using the TexttoColumns tool though as your macro doesn't go on to use them, so you can save two tools there.
@Chris, you are right, i was preparing with some other direction and then changed mind to use find & replace
yes it should work without the TTC tool :)
Nice catch :)
Thanks
Saravanan
being frustrated with this type "string" to "datetime" conversion. Paul's formula works on "Feb 22, 2015", but return "Null" for "Feb 2, 2015". I can't figure out a way to pad the single digit of the day in the middle with "0", nor i don't want to make a table of 96 values like "Jan 1" by using Find/Replace. please help me out!
many thanks,
Yingxin
Here is a sledge hammer approach:
Read your DATE (STRING) into a "Text to Columns" tool and parse [DATE] with a \s delimiter into 3 fields.
Formula Tool next for [DATE]
[DATE1]+' '+RIGHT(PADLEFT([DATE2],3,"0"),3)+[DATE3]
This will convert Feb 3, 2015 to Feb 03, 2015 and leave all other dates alone.
thanks it works. I thought some ways even heavier. like extract the day, trim space, convert to int, if <10 then *10 and reverse... while....
While I like the TTC solution as clear what it is doing. You can use a REGEX_Replace formula to add the missing 0 and then DateTimeParse will work.
The REGEX expression for this would be something like:
REGEX_Replace([Text], "([a-z]{3})\s*([0-9]{1}),","$1 0$2,")
Passing this into the DateTimeParse formula gives:
DateTimeParse( REGEX_Replace([Text], "([a-z]{3})\s*([0-9]{1}),","$1 0$2,"), "%b %d,%Y")
I tried in a range of dates from 2014 to present and seems to work.
Thanks! I was shy away of Regex becasue I can't fully grasp it. I understand the pattern part in Regex_replace, but not sure what ' "$1 0$2,") ' is doing. Thanks in advance if you or anyone here can enlighten me.
To break this down...
REGEX_Replace([Text], "([a-z]{3})\s*([0-9]{1}),","$1 0$2,")
The '$' sign and a number are indicating the 'marked groups' in the initial expression. A 'marked group' is whatever is within the parentheses with the number after the "$' sign indicating the order they are in.
So it is saying take any 3 alpha characters (the [a-z]{3}) and make that the first marked group, followed by 'zero or more spaces' (the \s*), followed by a single digit (the [0-9]{1}) making that the second marked group, followed by a comma.
Now it take the contents of the first marked group, and put a space and a '0' before the second marked group followed by a comma. It is essentially pulling out whatever is in the first and second marked groups, eliminating the space, and then putting the first and second marked groups back together with a space and '0' in between.
It doesn't do anything when the day has two digits since the expression is only looking for (and working on) an string with a single-digit day.
Hope this makes sense.