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

[binuacs]

@Sshasnk have you tried find and replace tool?

[Sshasnk]

@binuacs As I mentioned above the results are coming wrong using find and replace. Example -Dec should be 12 and Marry should be null. But it is not working but for some reason it works on contain function but I am not able to use contain because it will 100 lines of if else condition. But the result you posted is wrong, sorry 😔

[binuacs]

@Sshasnk I thought that was your expected result, can you post the expected result based on your input files?

[Sshasnk]

E Name | Month

Jun-Dec Client | 12 -> according to second dataset it should be 12

22/12 Tesla | 12 -> This is correct 

Marry ltd | NA -> no value matched

Tesla YE2023Dec31 | 12 -> this should be 12 according to second dataset 

Juno 2019 | NA -> no value matched 

[binuacs]

@Sshasnk Let me know the below approach works for you