nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Doubt with strings, replacements and dates

unknown

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

Date Time

Expression

Accepted answers

s_pichaipillai

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

DateFormat.yxmc

All comments

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

unknown

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?

s_pichaipillai

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

DateFormat.yxmc

s_pichaipillai

you can test the same with the attached sample workflow

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

DateFormat.yxmd

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

unknown

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

chris_love

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.

yingxin

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

Quick Links

Popular Tags

This months top contributors

atcodedog05 19598

Qiu 15906

binu_acs 15783

MarqueeCrew 13709

apathetichell 13703