convert month name to number

ll1100000

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.

Error Message

Accepted answers

Philip

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

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"))

All comments

Philip

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

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

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

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

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?

Damian_WRO

Spoiler

challenge_193_Damian.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703