How to convert a string to date

Sshasnk

Hi I have a dataset which has dates in it with different pattern for example below

E Name

Jun-Dec Client

22/12 Tesla

Marry ltd

Tesla YE2023Dec31

Juno 2019

For the above I have created another datasheet which is basically tells me the pattern and gives me the month for that record

E Name	Month
(12)	12
22/12	12
Sep	9
Mar	3
Dec	12
Jun	6
-Dec	12

Now I am joining both the records using find and replace tool, the first data goes to the find part and the second dataset goes to the replace part but because of this I am getting wrong results. Below is the result example which I am getting 

E Name	Month
Jun-Dec Client	6
22/12 Tesla	12
Marry ltd	3
Tesla YE2023Dec31	12
Juno 2019	6

So in the above results you can see the first is wrong because it should have picked the '-Dec' one, So as 3rd is also wrong because it is reading it as Mar and same goes with the last one as well.

Can you please let me know if this is the right approch and if not what I can change to make it correct.

Note: I cant use if else condition because the patter could go up in the large amount

simonaubert_bd

Hello @Sshasnk 

To be frank, I won't be optimistic there. Automation means you can specify a rule to find the date and from what I read, there is no rules that can really apply. Why is your dataset so diverse ? if it's because it comes from different sources, maybe you can apply rules by sources. if it's because the field you use is a "free text" input, I think it's dead to automate the thing (you can still iterate to find the dates and deal with your current dataset but you will have to have an eye on it when the data will change... I also advise you to see with the data provider to set rules in input).

Best regards,

Simon