Dynamic bucketing when input data can change

[sureslala]

Hello Community

I need to bucket financial instruments that have maturity dates (called Tenor's) into bucket periods. Because the instruments and tenors can change every time I want to report, these inputs need to be dynamic. (mostly tenors will change, say for a bond from 21/06/33 to 21/05/33 if I was reporting the bond mth on mth)

In my workflow I've created a table of dates based off my reporting date (in this case 28/06/2024) which correspond to bucket periods.

I have dates (in format dd/mm/yyyy) assigned to each bucket. For each instrument, the tenor dates could be in multiple formats (i.e. 104W, 10M, 01/11/32, 153W etc.) My problem is trying to bucket instruments with multiple format type tenors (which change every mth) into my bucket dates (dd/mm/yyyy) to give the correct bucket periods (example below in yellow).  

Attached file has sample instrument data with its 'system generated' tenor and the bucketing table. Blue column (D) is my expected output. 

After several days trying to crack this, any help would now be greatly appreciated! :)

Thank you Alteryx Community!

 Sures

[sureslala]

Hi Binuacs
I forgot to thank you for your assistance on my queries, it was very much appreciated. There's no way I would ever have got to your formula solutions, so again thank you for this!

Just for my own benefit, your formula below transform's a 'string type' tenor date to a 'date type'

If you wouldn't mind explaining, logically what does each line say it's doing?

Thanks

Sures

[binuacs · Accepted solution]

@sureslala  In the above formula the Regex_Match() function looking for the different pattern on the field [Tenor] and based on that it is converting to Date field

For eg: if the Tenor = 25/10/24 , then the regex_match checks the pattern which is \d{2}\/\d{2}\/{\d{2}} and then converting it into date field using the DateTimeParse function assuming that the given date is in the format of dd/mm/yy, if that pattern is not satisfying the given data it checks the next pattern whether the given field is in 2024-10-25 then converting to date and so on. let me know you need more explanation