Early bird tickets for Inspire 2023 are now available! Discounted pricing closes on January 31st. Save your spot!

Alteryx Designer Discussions

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

convert month name to number

ll1100000
8 - Asteroid

My month column is like below and I try to convert the month name to 2 digit number by using formula function.

Month
Feb
Mar
Apr

 

Here is what I keyed in, but I continue getting this error messages.  "Malformed function call" 

DateTImeFormat(DateTimeParse(ToNumber([month]), "%m")

 

How should I fix it?  Thank you.

7 REPLIES 7
Philip
12 - Quasar

Hi @ll1100000

 

The Month column isn't a date format, so the DateTime functions won't work on it. Instead, you can use the following in your Formula Tool.

 

SWITCH([Month], Null(),

"Jan", "01",

"Feb", "02",

"Mar", "03",

"Apr", "04",

"May", "05",

"Jun", "06",

"Jul", "07",

"Aug", "08",

"Sep", "09",

"Oct", "10",

"Nov", "11",

"Dec", "12"

)

jdunkerley79
ACE Emeritus
ACE Emeritus

I'd probably go with @Philip suggestion but:

 

ToNumber(substring(DateTimeParse([month], "%b"),5,2))

Will work. Or if you have my function library:

Month(DateTimeParse([month], "%b"))
luizbezerra
6 - Meteoroid
 
Perfect Solution!
I can transform the date JANEIRO/2017
for 01/2017 remembering that my files are not in English but in Portuguese, I do not know if there is a translator, or I need to use a case for the solution

 

 

MatthieuArzel
7 - Meteor

Did you manage to use a switch function to recursively replace your month name inside de string field? (i mean a complete date string baldy formated as "Date 4 janv. 2019" for example).

MatthieuArzel
7 - Meteor

That's indeed pretty clean. Though Alteryx cannot recognise localized shorten month name.

 

I'm trying to use the dynamic rename action to create proper date format for some columns and I seem to be stuck with some nasty dirty imbricated replace function (and I mean 12 in a row) to replace things like Mars, Août or Déc. into proper integers.

Would you have any advise to have a cleaner code? Isn't there a replace function able to get an assoc as a parameter to replace a key by its value?

KylieF
Alteryx Community Team
Alteryx Community Team

Hi @MatthieuArzel,

 

At this point localization in month names is not widely supported across our tools. My recommendation would be to use a Find Replace on the abbreviated months to convert them into either a numeric format or English format, at which point you can use the DateTime tool custom format feature to update your dates into a proper datetime format.

________________________________________________________
Program Manager - Community Platform, Alteryx
MatthieuArzel
7 - Meteor

Hi @KylieF 

 

That's a perfect solution. I do that already when i have to clean format inside my rows.

The trick here is that you can't use search and replace action then "feed" the dynamic rename action with the result.

I'm stuck in using only formula.

Labels