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.

[Philip · Accepted solution]

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 · Accepted solution]

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?

[KylieF]

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.

[MatthieuArzel]

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.

[Damian_WRO]

Spoiler