Hi @ADonia and welcome to the Knime Community.
There are definitely several ways to do this as @ArjenEX mentioned.
In addition to the options that @ArjenEX has suggested, you can also use the Rule Engine to add your rules - it would be similar to the Column Expressions solution, but instead of using coding and if…else, you would use the Rule Engine syntax.
Another way that I wanted to use is using the Extract Date&Time Fields node that can extract the month number, provided that we have a valid date. So using this method, I don’t need to create a dictionary or Rules for the month numbers. It avoids me having to manually build a dictionary or manually write the rules. More importantly, it avoids mistakes as there are quite a lot rules to write (I do see a couple of mistakes from @ArjenEX 's screenshots - or perhaps that was on purpose)
This is how I achieved this:
Input (a subset of your data):
I first extract the month using the Cell splitter:
There’s a date format that is accepted with month such as: Jan, Feb, Mar, Apr, etc… basically the capitalized version of the first 3 letters of the month name, therefore I used the
substring(month, 0, 3) which will give me the first 3 letters which will work whether it’s a full name or already with 3 letters.
I generate a valid dummy date using the formatted month name:
And this gives me this:
To convert to type date, I use the following settings:
Choose type Date, Locale as en-US and Date format as yyyy-MMM-dd
Note: This Date format (MMM) works only with Locale en-US
I get these dates after converting:
Then I use the ability to extract the month number by the Extract Date&Time Fields node:
And I get this:
After that, I just need to run a replace() function using values from the 2nd and 4th column as search and replace in the 1st column:
Here’s the workflow: Date String to Number.knwf (16.6 KB)
Note: Please provide some data that we can copy and paste that we can work with in the future