Doubt with strings, replacements and dates

[Inactive User]

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

[paul_houghton]

Hey Pablo, with a formula tool try using datetimeparse([date_string],'%b-%Y'). The %b modifier should show the abbreviated month name (Feb, Oct)

Have a look at the help file it has a list of the date modifiers there

[Inactive User]

Hi Paul,

Thanks for the tip! bu if my months are not in english? I think Alteryx doesn't recognize the months so then I should change all of them. It's not possible to use one unique formula to concatenate different replacements? instead of updating the output field once for every formula?

[MarqueeCrew]

How about nesting your replacements?

replace(REPLACE(replace([String],"Jan","01"),"Feb","02"),"Mar","03)

[paul_houghton]

Ah I don't know how Alteryx treats other languages but I would think you are correct. What you would need to do would be translate the fields to english (replace 'foreign Jan', 'Jan') , etc then do the date parsing on it. Those two steps could be combined into a single formula tool but it would separate formulas for each.

[MarqueeCrew]

For a client using Spanish, I created a text table that associates the Month # with Spanish terms.  I used a Find&Replace tool to switch between English and Spanish.

[tom_montpool]

You could also use the Switch() function to convert from English to Spanish.

[s_pichaipillai · Accepted solution]

Hi

I created a macro for your date conversion as i dont see any direct function 

1. created a text input for both english and spanish

2. this macro accepts both format something below

then use this macro to convert your string data into date

the final worflow and result would be like this

PFA macro for your testing 

[s_pichaipillai]

you can test the same with the attached sample workflow

FYI both macro and workflow created with 10.0 version :-)

[jdunkerley79]

You could use a switch statement picking out the part of the input you need.

So to support English and Spanish:

SWITCH(LOWERCASE(LEFT([Field1],3))
       ,NULL() // Default
       ,"jan","01","feb","02","mar","03","apr","04","may","05","jun","06","jul","07","aug","08","sep","09","oct","10","nov","11","dec","12" // English
	,"ene","01","feb","02","mar","03","abr","04","may","05","jun","06","jul","07","ago","08","sep","09","oct","10","nov","11","dic","12" // Spanish
)

Would be easy to generate each language you need